Excel SUMPRODUCT to Count Multiple Criteria

Advanced calculator detail
Photography taken by Mario Gutiérrez. / Getty Images

The COUNTIFS function, which can be used to count up the number of times data in two or more ranges of cells meets multiple criteria was first introduced in Excel 2007. Prior to that, only COUNTIF, which is designed to count the number of cells in a range that meets a single criterion, was available.

For those using Excel 2003 or earlier versions, or for those wanting an alternative to COUNTIFS, rather than trying to figure out a way to count multiple criteria using COUNTIF, the SUMPRODUCT function can be used to instead.

As with COUNTIFS, the ranges used with SUMPRODUCT must be of identical size.

Further, the function only counts instances where the criterion for each range is met simultaneously - such as in the same row.

How to use the SUMPRODUCT Function

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

=SUMPRODUCT( Criteria_range-1, Criteria-1) * (Criteria_range-2, Criteria-2) * ...)

Criteria_range - the group of cells the function is to search.

Criteria - determines whether the cell is to be counted or not.

In the example below, we will count only the rows in the data sample E1 to G6 that meet specified criteria for all three columns of data.

The rows will only be counted 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.

Example Using Excel SUMPRODUCT Function 

Note: Since this is a non-standard use of the SUMPRODUCT Function, the function cannot be entered using the dialog box, but must be typed into the target cell.

  1. Enter the following data into cells E1 to E6: 1, 2, 1, 2, 2, 8.
  2. Enter the following data into cells F1 to F6: 4, 4, 6, 4, 4, 1.
  3. Enter the following data into cells G1 to G6: 5, 1, 5, 3, 8, 7.
  4. Click on cell I1 - the location where the function results will be displayed.
  5. Type the following into cell I1:
    1. = sumproduct ((E1:E6<=5)*(F1:F6=4)*(E1:E6>=5)) and press the Enter key on the keyboard.
  6. The answer 2 should appear in cell I1 since there are only two rows (rows 1 and 5) that meet all three of the criteria listed above.
  7. The complete function = SUMPRODUCT ((E1: E6<=5)*(F1: F6=4)*(E1: E6>=5)) appears in the formula bar above the worksheet when you click on cell I1.