COUNTIFS based on complex criteria

Question

      A     B     C 

1     M     2233  1R
2     M     1122  1 
3     B     3311  0R
4     S     3333  0 
5     M     0033  1R

From the following table, I would like to count every row in column C that contains an "R" but only if column A contains "M" and the last two characters of column B are "33". So the output I would like to get is 2 (C1+C5). Note that column B is formatted as text.

So far, I have tried COUNTIFS(A1:A5,"=M",B1:B5,MID(B1:B5,3,2)="33",C3:C5,"=*R") as an array formula without luck.

I know one problem is that MID(B1:B5,3,2)="33" isn't being treated as an array formula but I'm at a loss on how to fix it.

Any takers?

Answer 1

You can use this I think:

=COUNTIFS(C1:C5,"*R*",A1:A5,"M",B1:B5,"*33")

Using wildcards * to indicate where values can be different.

Answer 2

The following gives the result you're after:

  =COUNTIFS(A1:A5,"=M",B1:B5,"=*33",C1:C5,"=*R*")

This counts the rows from 1 to 5 where A is 'M', B ends with '33' and C contains 'R'. Note that your original formula wasn't helped by only counting C3:C5.

Answer 3

What's crucial here is how column B is formatted - wildcards don't work with numeric values so you can only use COUNTIFS if column B is numeric, i.e. this formula:

=COUNTIFS(A1:A5,"M",B1:B5,"??33",C1:C5,"*R")

if that doesn't work because column B is numeric then try SUMPRODUCT like this

=SUMPRODUCT((A1:A5="M")*(RIGHT(B1:B5,2)="33")*(RIGHT(C1:C5)="R"))

That doesn't explicitly ensure that column B has 4 characters with 33 as the last two (as COUNTIFS does), merely that the last 2 characters are 33 - if you need to check that there are 4 characters use this version:

=SUMPRODUCT((A1:A5="M")*(RIGHT(B1:B5,2)="33")*(RIGHT(C1:C5)="R")*(LEN(B1:B5)=4))