Have you ever had trouble trying to count items in an Excel list, based on two criteria? See how to use the Excel SUMPRODUCT function to get the count that you need
Use SUMPRODUCT to Count
Instead of using the COUNT function, or the COUNTA funtion, you can use the SUMPRODUCT function to count items based on 2 criteria.
In the example shown below, the SUMPRODUCT function is used to count the rows where :
- the item sold is “Pen”
- AND the quantity is greater than or equal to 10
Add SUMPRODUCT Formula
This solution will work in any version of Excel, including Excel 2003 or earlier, where there COUNTIFS function is not available.
- Select the cell in which you want to see the total
- Type an equal sign (=) to start the formula
- Type: Â Â SUMPRODUCT(–(
- Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
- Type the first criterion: Â Â =”Pen”
Note: Because this is a text criterion, it is enclosed in double quote marks. - Type ),–(
- Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
- Type the second criterion: Â Â >=10
Note: Because this is a numerical criterion, it is NOT enclosed in double quote marks. - Finish with closing brackets: ))
- The completed formula is shown in the screen shot below.
- =SUMPRODUCT(–(A2:A10=”Pen”),–(B2:B10>=10))
- Press the Enter key to complete the entry
Use Cell References
Instead of typing the criteria in a formula, you can refer to a cell, as shown in the second formula below.
- Use typed criteria:
=SUMPRODUCT(–(A2:A10=”Pen”),–(B2:B10>=10))
2. Or use cell references:
=SUMPRODUCT(–(A2:A10=D2),–(B2:B10>=E2))
More Excel Count Examples
There are many more examples, written steps, and videos for counting in Excel on my Contextures website.
Count Criteria in Other Column
Count Cells With Specific Text
____________
Nice animation…I never knew Underdog was an Excel guy…
You can use SUMPRODUCT() to generate SUMIF() results with multiple criteria well. For example, if column D held the actual tip amounts, you could sum up the total tips from Sunday customers with the following formula:
=SUMPRODUCT(–(B2:B8=”Sun”), –(C2:C8=”Yes”), D2:D8)
For 2007 use CountIfs its much much faster but will work for AND criteria only
Hi Debra, that video is cool!
A B C
NICE 7/17/2009 F
NICE 7/17/2009 A
NICE ONE A (*)
NICE 6/12/2008 A (*)
NICE 2/24/2009 A (*)
NICE TWO A (*)
How to build a sumproduct formula to count date and text (*), when Col A=NICE and C=A and less than 7/17/2009, the answer should be 4.