Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I count records when I have more then one criteria?
Best of Excel
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
4=ö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
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
4=ö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