In this article, we will focus on how to use SUMPRODUCT function with criteria in 2 columns & extract the output, meeting all the conditions.
I have a three columns data comprising of Months in column A, Sales Rep in column B & Sales Quantity in column C. Now, I want to calculate the quantity sale by Rep John & James from January month.
SUMPRODUCT: Returns the sum of the products of corresponding ranges or arrays.
array1,array2,….. array255 are the ranges of cells or arrays that you want to multiply & then add all of them.
There can be maximum 255 arrays in Sumproduct function. Refer below screenshot:-
Following is the snapshot of data we have:
- In cell E2, the formula is
In case the criteria change as following:
- Month= Jan & Feb
- Rep=John & James
To get the output, we will use SUMPRODUCT function as follows:
- In Cell E2, the formula is
- The same result can be achieved through Filters but this requires user to first apply filter & then set criteria in Month & Rep column.
- Using SUMPRODUCT function we can avoid using filters in Excel.
In this way, we can use Sumproduct function to work with multiple conditions in two columns & update the quantity sale.