Excel Count With 2 Criteria-SUMPRODUCT

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.

  1. Select the cell in which you want to see the total
  2. Type an equal sign (=) to start the formula
  3. Type:   SUMPRODUCT(–(
  4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
  5. Type the first criterion:   =”Pen”
    Note: Because this is a text criterion, it is enclosed in double quote marks.
  6. Type ),–(
  7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
  8. Type the second criterion:   >=10
    Note: Because this is a numerical criterion, it is NOT enclosed in double quote marks.
  9. Finish with closing brackets: ))
  10. The completed formula is shown in the screen shot below.
    • =SUMPRODUCT(–(A2:A10=”Pen”),–(B2:B10>=10))
  11. Press the Enter key to complete the entry
Excel Count multiple criteria with SUMPRODUCT
Excel Count multiple criteria with SUMPRODUCT

Use Cell References

Instead of typing the criteria in a formula, you can refer to a cell, as shown in the second formula below.

  1. 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 / COUNTIF Examples

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

____________

0 thoughts on “Excel Count With 2 Criteria-SUMPRODUCT”

  1. 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)

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

Leave a Reply to Katheryn Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.