Results 1 to 8 of 8
  1. #1
    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. #2
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

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

  4. #4
    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. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Glad that it helped.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Apr 2009
    Posts
    1

    Lightbulb 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. #7
    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

    Thank you in advance!

  8. #8
    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
  •