Countifs

[Tcurtis]

Good morning! I have a issue using countifs. I have a column of data that contains 3 characters - I, M and B. I want to only count I and M. I am currently using {=COUNTIFS(Paste!P:P,"I",Paste!P:P,"M ")} without {} but I only get 0 as the result.
Paste is the worksheet that the data is on and P is the column that contains the data. This seems pretty straight forward to do but for some reason I cannot get the formula to count the criteria I have set up.

 

[SteveO59L]

=countif(p,"m")+countif(p,"i")

 

[Jonmo1]

Try

=SUM(COUNTIF(Paste!P:P,{"I","M"}))

OR

If I M and B are the ONLY options for column P (no blanks either) then simply
=COUNTIF(Paste!P:P,"<>B")

 

[Mike LH]

Hi,

Try this one. Note I cut this down to 5000 rows because using full columns slows things down too much. Use more rows if you need to but avoid full columns.


=SUMPRODUCT(--(ISNUMBER(MATCH(Paste!P1:P5000,{"M","I"},0))))

 

[Tcurtis]

Works great, thanks!!!

 

[Jonmo1]

You're welcome.