Count data cells with SUMPRODUCT function in Excel

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

Remark . The 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.

  1. SUMPRODUCT Function Syntax and Arguments
  2. Enter the SUMPRODUCT function
  3. Destruction of SUM PRODUCT

SUMPRODUCT Function Syntax and Arguments

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments.

Use the following syntax with SUMPRODUCT to get a function to count cells and not store data:

 = SUMPRODUCT ( массив1 ,  массив2 ) 
  • Matrix1 . This argument specifies the first array or range to be multiplied and then added.
  • Array2 . This argument specifies the second array or range to be multiplied and then added.

The COUNTIF and COUNTIF functions count cells that match one or more specified criteria. Sometimes SUMPRODUCT is easier to use when you want to find multiple terms in the same range.

Enter the SUMPRODUCT function

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

However, because this example uses an incorrect form of the SUMPRODUCT function, the dialog box cannot be used. Instead, the function must be entered in a worksheet cell.

In this tutorial, you use the SUMPRODUCT function to find the number of values ​​greater than 25 and less than 75 in a sample data set.

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

  2. Select cell B7 . Displays the results of the function.

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

  4. 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.

  5. Select in a cell B7 to view the completed formula in the formula bar above the worksheet.

Destruction of SUM PRODUCT

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

Another way to think of what SUMPRODUCT does is to think of the multiplication sign as an AND condition. With that in mind, the condition is true only if both conditions are true, numbers greater than 25 and less than 75. The function then counts all the true values ​​to get result 5.

Leave a Reply

Your email address will not be published. Required fields are marked *