How to apply COUNTIFS Formula against cells on same row within a range

Question

I am trying to count the number of occurences in a range based on 2 criteria. The first criteria is based on Text, the second criteria is based on 2 cells in the repsective row of a range. I cannot get the count to work - it counts incorrectly.

Example....

      COL A       COL B       COL C
ROW 1 Eggs        2           3
ROW 2 Eggs        3           1
ROW 3 Eggs        4           9
ROW 4 Eggs        1           2
ROW 5 Bacon       2           1
ROW 6 Eggs        4           1

The formula being applied is:

=COUNTIFS($A$1:$A$6,"Eggs",$B$2:$B$6,">"&$C$2:$C$6)

It doesnt work correctly......

I would expect the result to be a count of 2, as there are 2 occurences of Eggs where column B is greater than column C

Your formula as posted will result in an error because your ranges are not the same size. Anyway, it won't work because you need an array formula; COUNTIF requires a single criteria. It might work if you enter it as an array formula. — SJR

Unknown Unknown · Accepted Answer · 2019-01-16T19:44:22

Your ranges are not the same size and you should likely be using SUMPRODUCT instead of COUNTIFS.

If you have a header row in row 1,

=SUMPRODUCT(--($A$2:$A$7="eggs"), --($B$2:$B$7>$C$2:$C$7))

If there is no header row and your data starts in row 1,

=SUMPRODUCT(--($A$1:$A$6="eggs"), --($B$1:$B$6>$C$1:$C$6))

How to apply COUNTIFS Formula against cells on same row within a range

1 Answers