Using multiple countifs to return result based on multiple columns

Hi guys,

I'm trying to use 2 countifs in one formula as shown in the attachment.

I would like Report!B3 to look at the department in Report!B1, look for that in Blocks!A2:A11, and count the number of "blocked bins".

The problem I've been having is that there are 2 columns depicting block types, so I tried countifs+countifs and countifs*countifs but I'm not getting the desired result.

If Blocks!B:B contains a X then that counts as a block, same as if there is a X in Blocks!C:C, same as if there is a X in both columns.

Therefore, the result that I'm trying to get in highlighted cell Report!B2 is 3 (bins in rows 2, 3 and 4 on Blocks! tab).

I hope this makes sense.

One quick and dirty way:

Formula:

Please Login or Register  to view this content.

BSB

Thanks BadlySpelledBuoy. This works in the example spreadsheet but for some reason when I move it to my actual spreadsheet and change the formula to match the new cell range, it provides the correct number but with a minus in front of it. Any idea why it would do that?

To answer that I'd need to see the file with the formula in place.

Any chance of a desensitized version?

BSB

Hi BSB, it turns out that it was due to the source data not being in number format, I didn't think that would cause this kind of an anomaly.

Thanks for your help on this thread.

Glad I could help

Thanks for the rep.

BSB