Count Cells of Data with Excel's SUMPRODUCT Function

 The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the arguments entered.

What the SUMPRODUCT function normally does is 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 04

SUMPRODUCT vs. COUNTIF and COUNTIFS

Using SUMPRODUCT to Count Cells of Data in Excel
Using SUMPRODUCT to Count Cells of Data. © Ted French

Since Excel 2007, the program also has the COUNTIF and COUNTIFS functions that will 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 as is shown in example located in the image above.

02
of 04

SUMPRODUCT Function Syntax and Arguments to Count Cells

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

To get the function to count cells rather than perform its standard purpose, the following non-standard syntax must be used with SUMPRODUCT:

= SUMPRODUCT ([condition1] * [condition2])

An explanation of how this syntax works is outlined below the following example.

Example: Counting Cells that Meet Multiple Conditions

As shown in the example in the image above, SUMPRODUCT is used to find the total number of cells in the data range A2 to B6 that contain data between the values of 25 and 75.

03
of 04

Entering the SUMPRODUCT Function

Normally, the best way to enter functions into Excel is to use their dialog box, which makes it easy to enter the 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 dialog box approach cannot be used. Instead, the function must be typed into a worksheet cell.

In the image above, the following steps were used to enter SUMPRODUCT into cell B7:

  1. Click on cell B7 in the worksheet - the location where the function results will be displayed
  2. Type the following formula into cell E6 of the worksheet:

    =SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75))

  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  =SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)) appears in the formula bar above the worksheet
04
of 04

Breaking Down the SUMPRODUCT Function

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 array elements that are FALSE.

The corresponding ones and zeros in each array are multiplied together:

  • with 1 x 1 returning a value of 1
  • with 1 x 0 returning a value of 0

These ones and zeros are then summed by the function to give us a count of the number of values that meet both conditions.

Or, think of it this way...

Another way to think of what SUMPRODUCT is doing is to think of the multiplication sign as an AND condition.

With this in mind, it is only when both conditions are met - numbers greater than 25 AND less than 75 - that a TRUE value (which is equal to one remember) is returned.

The function then sums up all the true values to arrive at the result of 5.

Format
mla apa chicago
Your Citation
French, Ted. "Count Cells of Data with Excel's SUMPRODUCT Function." ThoughtCo, Jan. 11, 2018, thoughtco.com/excels-sumproduct-function-3123572. French, Ted. (2018, January 11). Count Cells of Data with Excel's SUMPRODUCT Function. Retrieved from https://www.thoughtco.com/excels-sumproduct-function-3123572 French, Ted. "Count Cells of Data with Excel's SUMPRODUCT Function." ThoughtCo. https://www.thoughtco.com/excels-sumproduct-function-3123572 (accessed January 16, 2018).