CODEWITHSUNDEEP
matchspreadsheetoffsetcountif
topstuff
topstuff

Reputation: 129

COUNTIF based on three conditions using OFFSET and MATCH

Please see example screengrab

I would like to populate cell M2. Firstly to match K2 (Taylor) against column headers C1:I1 looking at the results in the column C2:C32. I would like to find the amount of times "a" appears in C2:C32 where Type (Column B) = "r".

So the result would be 3 (Reynolds, Maggio & Hamilton).

As you can see I've managed to populate Column R with totals without comparing against Type (Column B) but am having great difficulty understanding how to extend the comparison, intentionally without the use of helper columns/rows.

Any help would be greatly appreciated.

Upvotes: 1

Views: 473

Answers (1)

Jerry
Jerry

Reputation: 71558

Since you have to depend on 2 columns, you will have to use COUNTIFS. Without being dynamic, the formula for M2 would be:

=COUNTIFS($B$2:$B$32,"r",$C$2:$C$32,"a")
          ^------------^ ^------------^
           1st Condition  2nd Condition

To make it dynamic, only the second column needs to be changed:

=COUNTIFS($B$2:$B$32,"r",OFFSET($B$2:$B$32,0,MATCH($K2,$C$1:$I$1,0)),"a")

Your total's formula could be simplified to this also (keep the range as it is instead of manually putting it as 32 rows high for instance):

=COUNTA(OFFSET($B$2:$B$32,0,MATCH($K2,$C$1:$I$1,0)))

Upvotes: 1

Related Questions