# Thread: Excel, countif, multiple columns

1. Registered User
Join Date
May 2002
Posts
157

## Unanswered: Excel, countif, multiple columns

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

2. Registered User
Join Date
Oct 2003
Posts
1,091
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.

3. Registered User
Join Date
May 2002
Posts
157
Thank you so much - works perfectly
regards
Karen

4. Registered User
Join Date
Oct 2008
Location
Northwest Indiana
Posts
3
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!

5. Registered User
Join Date
Oct 2003
Posts
1,091

6. Registered User
Join Date
Apr 2009
Posts
1

## too slow

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")
Last edited by ExpIorer; 04-27-09 at 08:42.

7. Registered User
Join Date
Aug 2011
Posts
1

## Expanding on More than one column for Countif

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

8. Registered User
Join Date
May 2002
Posts
157

## Did you get an answer on this?

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•