Use Excel's SUMPRODUCT Function to Count Multiple Criteria

Count cells in ranges

When you want to count the number of times data in two or more ranges of cells meets multiple criteria, use the SUMPRODUCT function. SUMPRODUCT only counts instances where the criterion for each range is met simultaneously, such as in the same row.

The information in this article applies to Excel for Microsoft 365, Excel 2016, Excel 2013, Excel 2010, Excel Online, and Excel for Mac.

How to Use the SUMPRODUCT Function

The syntax used for the SUMPRODUCT function when using it to count multiple criteria is different than generally used by the function:

  • Criteria range — The group of cells the function is to search.
  • Criteria — Determines whether the cell is to be counted or not.

In our example, we will count the rows in the data sample, cells E1 to G6, that meet specified criteria for all three columns of data. ExcelwillcounttThe rows if they meet the following criteria:

  • Column E — If the number is less than or equal to 2.
  • Column F — If the number is equal to 4.
  • Column G — If the number is greater than or equal to 5.

Use Excel's SUMPRODUCT Function 

Since this is a non-standard use of the SUMPRODUCT function, the function cannot be entered using the Formula Builder. It must be typed into the target cell.

  1. Enter the example data, as shown, into a blank Excel worksheet.

    Data in Excel worksheet
  2. Select cell F8, which is where the function results will display.

    Cell F8 selected in worksheet
  3. Type the following into cell F8 and press Enter.

    =SUMPRODUCT((E1:E6<=5)*(F1:F6=4)*(G1:G6>=5))

    SUMPRODUCT formula in Excel
  4. The answer 2 appears in cell F8 since there are only two rows (rows 1 and 5) that meet all three of the criteria listed above.

    Excel showing the function result
    Screenshot 

The complete function appears in the formula bar above the worksheet when you select cell F8.