Count Cells of Data With Excel's SUMPRODUCT Function

Calculator, pen, notebook, and pencils on desk

Towfiqu Photography / Getty Images

The SUMPRODUCT function multiplies the elements of one or more arrays and then adds, or sums, the products together. By adjusting the form of the arguments, SUMPRODUCT counts the number of cells in a given range containing data that meets specific criteria.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

SUMPRODUCT Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

To get a function to count cells rather than hold data, the following syntax is used with SUMPRODUCT:

=SUMPRODUCT(array1,array2)
  • Array1: This argument denotes the first array or range that will be multiplied and then added.
  • Array2: This argument denotes the second array or range that will be multiplied and then added.

The COUNTIF and COUNTIFS functions count cells that meet one or more set criteria. At times, SUMPRODUCT is easier to use when you want to find multiple conditions that relate to the same range.

Enter the SUMPRODUCT Function

Normally, the best way to enter functions into Excel is to use the Function Arguments dialog box (in Excel for Mac, use the Formula Builder). The dialog box makes it easy to enter arguments one at a time without having to enter the brackets or the commas that act as separators between the arguments.

However, because this example uses an irregular form of the SUMPRODUCT function, a dialog box cannot be used. Instead, the function must be typed into a worksheet cell.

In this tutorial, you'll use the SUMPRODUCT function to find the number of values that are greater than 25 and less than 75 in a sample dataset.

  1. To follow along with this tutorial, enter the sample data (shown in the image below) in a blank Excel worksheet.

    Screenshot showing tutorial data for the SUMPRODUCT function in Excel
  2. Select cell B7. This is the location where the function results will display.

  3. Enter the formula =SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)) and press Enter.

    Screenshot showing the completed SUMPRODUCT function in Excel
  4. The answer 5 appears in cell B7. There are only five values in the range (40, 45, 50, 55, and 60) that are greater than 25 and less than 75.

    Screenshot showing the completed SUMPRODUCT function in Excel
  5. Select cell B7 to view the completed formula in the formula bar above the worksheet.

Breaking Down SUMPRODUCT

When conditions are set for the arguments, SUMPRODUCT evaluates each array element against the condition and returns a Boolean value (TRUE or FALSE). For the purposes of calculations, Excel assigns a value of 1 for those array elements that are TRUE and a value of 0 for those that are FALSE.

Another way to think of what SUMPRODUCT is doing is to think of the multiplication sign as an AND condition. With this in mind, the condition is only true when both conditions are met, numbers greater than 25 AND less than 75. The function then sums up all the true values to arrive at the result of 5.