Count Cells of Data with Excel's SUMPRODUCT Function

Financial notebook and pen on wooden table
Towfiqu Photography / Getty Images

The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the arguments entered. What the SUMPRODUCT function does typically is to multiply the elements of one or more arrays and then add or sum the products together. But by adjusting the form of the arguments, SUMPRODUCT will count the number of cells in a given range containing data that meets specific criteria.

01
of 03

SUMPRODUCT Function Syntax and Arguments

Screenshot of Excel showing Formula Builder with SUMPRODUCT

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 perform its standard purpose of holding data, the following non-standard syntax must be used with SUMPRODUCT:

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

Excel also contains both the COUNTIF and COUNTIFS functions, that allow you to count cells that meet one or more set criteria. At times, however, SUMPRODUCT is easier to work with when it comes to finding multiple conditions relating to the same range.

02
of 03

Entering the SUMPRODUCT Function

Screenshot of Excel showing the SUMPRODUCT Formula

Normally, the best way to enter functions into Excel is to use the Formula Builder, which 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, the Formula Builder cannot be used. Instead, the function must be typed into a worksheet cell.

 = SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75))
  1. Click on cell B7— the location where the function results will be displayed.
  2. Type the formula noted above into cell E6 of the worksheet.
  3. The answer 5 should appear in cell B7 as there are only five values in the range — 40, 45, 50, 55, and 60 — that are between 25 and 75.
  4. When you click on cell B7 the completed formula appears in the formula bar above the worksheet.
03
of 03

Breaking Down SUMPRODUCT

Prime numbers
ROBERT BROOK/SCIENCE PHOTO LIBRARY / Getty Images

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 for those array elements that are TRUE and a value of 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.