CODEWITHSUNDEEP
excelexcel-formulavba
baelaelael
baelaelael

Reputation: 57

Using COUNTIFS on Multiple Columns

I have a table named "Input" that takes the form below, with the actual table extending to around 20 columns and over 1000 rows. The "Site" column on the left will regularly have repeat data, but it will never be the case that, for example, W01 appears twice in the same row - though Site01 may have W01 in twice, just in different rows.

Site   |   Item1   |   Item2   |   Item3 
------------------------------------------
Site01 |    W01         W02        S01
Site02 |    W01         S02
Site03 |    S01         W01
Site01 |    W01

I then have another table called "Results" with all of the sites along the top row and all of the items along the leftmost column. The idea would be to have the table show how many times a site used a given item, so the result from the above table should be:

       | Site01 | Site02 | Site03 
 ---------------------------------
  W01  |   2        1        1
  W02  |   1        0        0
  S01  |   1        0        1
  S02  |   0        1        0

It seems relatively easy to do this over one column in the Input table using a COUNTIFS formula that takes the form:

=COUNTIFS('Input'!$C$4:$C$1000,'Results'!C$2,'Input'$D$4:$D$1000,'Results'!$B3)

Which can then be copied across the entire "Results" table.

I can use this approach over two or three columns by stringing together multiples of the above COUNTIFS with pluses, and just changing the next COUNTIFS to check 'Input'$E$4:$E$1000 in the second part of the COUNTIFS - but this quickly becomes unfeasible over a sheet with up to 20 rows - so I need a formula or VBA code which does exactly as this COUNTIFS approach does, but that scales over a much larger number of rows.

Upvotes: 0

Views: 2741

Answers (1)

XOR LX
XOR LX

Reputation: 7762

Switch to SUMPRODUCT:

=SUMPRODUCT((Input!$C$4:$C$1000=Results!C$2)*(Input!$D$4:$F$1000=Results!$B3))

Amend the F in the portion:

Input!$D$4:$F$1000

as desired.

Regards

Upvotes: 1

Related Questions