To count the number of times data in two or more ranges of cells matches multiple criteria, use the SUMPRODUCT function. SUMPRODUCT only counts cases where the criteria for each range are met at the same time, for example in the same row.
Remark . The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel Online, Excel for Mac, Excel for Android, Excel for iPhone, and Excel for iPad.
How to use SUMPRODUCT function?
The syntax used for the SUMPRODUCT function when it is used to count multiple criteria is different from the one normally used by the function:
= SUMPRODUCT (Criteria_range-1, критерии-1) * (Criteria_range-2, критерии-2) * ...)
- Criteria_range: The group of cells for which the function is intended.
- criteria . Indicates whether the cell should be counted or not.
In our example, we count the rows in the sample data, cells E1-G6, that meet the given criteria for all three columns of data. Rows are 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 4.
- Column G . If the number is greater than or equal to 5.
Use the Excel SUMPRODUCT function
Because this is a non-standard use of the SUMPRODUCT function, it cannot be entered with the Formula Builder. It should be printed in the target cell.
Enter the sample data as shown in the above screenshot into a blank Excel sheet.
Select cell F8 . Displays the results of the function.
Type the following in cell F8:
= СУММПРОИЗВ ((Е1: Е6 = 5))
and press Enter .
The answer 2 appears in cell F8 because there are only two rows (rows 1 and 5) that meet all three criteria above.
The full function is displayed on formula bars above the worksheet when cell F8 is selected.