×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Microsoft: Office FAQ

Best of Excel

How do I count records when I have more then one criteria? by bluedragon2
Posted: 15 Jan 04 (Edited 28 Jan 04)

The Problem:  I need to count all the rows based on 2 or more criteria

Bright 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    C
1    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    C
1    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 results
1    1    1    1
2    1    1    1
3    1    0    0
4    0    1    0
5    0    0    0
6    0    0    0
7    0    0    0

In other words:

True x True    or    1 x 1 = 1
True x True    or    1 x 1 = 1
True x False    or    1 x 0 = 0
False x True    or    0 x 1 = 0
False x False    or    0 x 0 = 0
False x False    or    0 x 0 = 0
False x False    or    0 x 0 = 0

The SUM part of the formula will add the PRODUCT results:

1 + 1 + 0 + 0 + 0 + 0 + 0 = 2

And 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    D
1    Zebra    10    Europe    Old
2    Zebra    5    Europe    Old
3    Zebra    10    Asia    New
4    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    0
1    0    0    0
1    1    1    1
0    1    1    1

The multiplication results would look like:

1 x 1 x 1 x 0 = 0
1 x 0 x 0 x 0 = 0
1 x 1 x 1 x 1 = 1
0 x 1 x 1 x 1 = 0

And, 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

My Archive

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close