Count if multiple criteria are met

[Jasesair]

For some unknown reason, I'm struggling to write a formula that counts the number of rows that some criteria are met. I've tried both countif and sumproduct without success. Any ideas?

=COUNTIFS($A:$A,$BK$8,$C:$C,$BL$8,$D:$D,$BM$8,$H:$H,"3")

=SUMPRODUCT(($A:$A=$BK$8)*($C:$C=$BL$8)*($D:$D=$BM$8)*($H:$H="3"))

I must be doing something fundamentally wrong, but I just can't see it!

 

[KRice]

Try the formula without quotes around 3

 

[Jasesair]

Yeah, tried that already unfortunately. Feel like I've tried everything!

 

[KRice]

What do you get?...an error or 0? Are there rows where you know all of those conditions are met at the same time?

 

[DeficientOptimism]

It’s very hard to assess without seeing the range, it’s format and the criteria ranges. It could be one of many things.

However, try taking the 3 out of the double quotations. In general, text values need to be included in quotations unless there’s a logical operator such as “>3”. Since it is just equal to 3, just leave it as 3 without quotes and see if that works first. This is assuming that your count range with 3 inside is formatted as a number value and not a text value.

 

[Michael M]

Maybe this way, but I'd reduce the ranges rather than using entire columns

=SUMPRODUCT(($A:$A=$BK$8)+($C:$C=$BL$8)+($D:$D=$BM$8)+($H:$H=3))

 

[DeficientOptimism]

It’s very hard to assess without seeing the range, it’s format and the criteria ranges. It could be one of many things.

However, try taking the 3 out of the double quotations. In general, text values need to be included in quotations unless there’s a logical operator such as “>3”. Since it is just equal to 3, just leave it as 3 without quotes and see if that works first. This is assuming that your count range with 3 inside is formatted as a number value and not a text value.

Alternatively, place a helper column in with 1 for every value to the bottom of your data set. Now perform a sumifs on this range with the same criteria.

 

[Jasesair]

Apologies everyone. As I was doing up an example, I realised my error. I had a word + number within each cell in one of the columns, rather than simply the number it was trying to find. Bit of a lesson there for rookies!!!

 

[DeficientOptimism]

Apologies everyone. As I was doing up an example, I realised my error. I had a word + number within each cell in one of the columns, rather than simply the number it was trying to find. Bit of a lesson there for rookies!!!

It happens to us all at some point!
No worries