Hi there,

I have say 3 columns of data in an excel spread sheet.

Column A = Multiple Names
Column B = MO, VOL, FWD
Column C = Mr, Mrs, Ms, Miss

So
Column A Column B Column C
John Doe MO Mr
Mary Smith Vol Ms
Lucy Richardo Mo Mrs
William Shatner FWD Mr
John Smith MO Mr
etc

I want to count column C only counting MR if Column B has MO in it.

So in the above example I would end up with a count of 2.

Can anyone help me with this?

thanks heaps in advance for any assistance.

Regards
Karen

Multi-condition count is accomplished with SUMPRODUCT

=SUMPRODUCT(($B$2:$B$100="MO")*($C$2:$C$100="MR"))

Notice that you cannot refer to the entire column, and all conditions must refer to the same length range.

Thank you so much - works perfectly
regards
Karen

Thanks from me, too. I couldn't figure out how to make countif work for this situation, either. Sometimes slow and old is better, take's one to know one!

Glad that it helped.

If you need instant results on large tables these methods will be too slow (50 sumproducts took 2 seconds to my 1000 row table after each update).
My approach (takes just milliseconds in my case) is to create new column where you concatenate B and C columns and then use countif(newcolumn;"MrMO")

I have a similar situation:

I have several state abbreviations in 1 column and rankings in another and need to come up with a formula so I do not have to count all combinations for each state

Ex:

AZ -3
AZ -3
AZ -2
AZ -1
AZ 4
AZ 4

etc. Since I have to do this for each state, I wanted to have a static field that I can enter which state I want and then my data is populated by score:

EX:

AZ -3 2
-2 1
-1 1
4 2

Thank you in advance!

Did you get an answer on this?
Do you still need help?