×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Office FAQ

## Best of Excel

 How do I count records when I have more then one criteria? by bluedragon2 faq68-4725 Posted: 15 Jan 04 (Edited 28 Jan 04) The Problem:  I need to count all the rows based on 2 or more criteriaBright Light Shines:  Hey, this COUNTIF formula looks like it may workàThe power company:  Shuts off that bright light because of the multiple conditionsàThe Solution:  SUMPRODUCTàExample:    A    B    C1    Dogs    New    2    Dogs    New    3    Dogs    Old    4    Cats    New    5    Cats    Old    6    Mice    Old    7    Mice    Old    My criteria is to count how many new dogs we have:=SUMPRODUCT((A1:A7=öDogsö)*(B1:B7=öNewö))gives me an answer of 2à (but how you say)OK, lets take the table and show it in a way that the formula sees it:    A    B    C1    1    1    2    1    1    3    1    0    4    0    1    5    0    0    6    0    0    7    0    0    The first part of the formula (A1:A7=öDogsö) looks at the area A1 through A7 for the string ôDogsö.  Where it finds it, it will indicate a TRUE (which is represented with the number 1) for that cell.  Where it does not find it, it will indicate a FALSE (which is represented with the number 0) for that cell.The second part of the formula (B1:B7=öNewö) looks at the area B1 through B7 for the string ôNewö and does the same for when it finds it or not.Once it has done this, the PRODUCT part of the function (A1:A7=öDogsö)*(B1:B7=öNewö) is where we tell it to multiply these results together which will look like:    A    B    Multiplied results1    1    1    12    1    1    13    1    0    04    0    1    05    0    0    06    0    0    07    0    0    0In other words:True x True    or    1 x 1 = 1True x True    or    1 x 1 = 1True x False    or    1 x 0 = 0False x True    or    0 x 1 = 0False x False    or    0 x 0 = 0False x False    or    0 x 0 = 0False x False    or    0 x 0 = 0The SUM part of the formula will add the PRODUCT results:1 + 1 + 0 + 0 + 0 + 0 + 0 = 2And that was the answer we were looking for based on our criteriaàO.K. that was cool, but what about more then two criteriaàThe single SUMPRODUCT formula can be used for 2 to 30 arrays and works on the same principle for them:Lets say we have four criteria in a formula like:    A    B    C    D1    Zebra    10    Europe    Old2    Zebra    5    Europe    Old3    Zebra    10    Asia    New4    Monkey    10    Asia    New=SUMPRODUCT((A1:A4=öZebraö)*(B1:B4=10)*(C1:C4=öAsiaö)*(D1:D4=öNewö))The four array rows will be tested and would give results like:1    1    1    01    0    0    01    1    1    10    1    1    1The multiplication results would look like:1 x 1 x 1 x 0 = 01 x 0 x 0 x 0 = 01 x 1 x 1 x 1 = 10 x 1 x 1 x 1 = 0And, the SUM of the PRODUCTS will result in 1 match.This should give you a basic understanding on the POWERFUL functionality of the SUMPRODUCT formula.  I may be adding a second FAQ to incorporate other items such as Dates and æIn-betweensÆ.Blue Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!