After reading this article you will be able to sumproduct numbers on the basis of condition.
Adding multiple rows if criteria met and then on certain conditions SUMPRODUCT functions are being used in Excel. In case you want to sum of the product based on condition, you can apply this formula.
We have columns Price and Invoice quantity. Our assignment is to add all of the same quantity and then multiply them with another column, i.e. price in the given range and returns the addition of the products.
Formula to implement:
Download Your Example Excel File
We will be creating SUMIF formula along with SUMPRODUCT formula in Excel:
Formula Explanation (Step by Step)
Let’s break the formula into multiple small steps to understand its application.
Step 1: SUMIF($E$3:$E$14,$B$3:$B$8,$F$3:$F$14)
(17+15=32, 13+12=25, 32+10=42, 18+16=34, 20+25=45, 23+14=37)
It returns sum in the range (F3 to F14) based on the criteria (B3 to B8) i.e. with a similar text or a name for example all invoice quantity of table retruns (32), chair (25) and so on.
Step 2: SUMPRODUCT(32,25, 42,34, 45,37,$C$3:$C$8)
(32*50, 25*30, 42*80, 34*40, 45*100, 37*60)
In a first step it adds based on the certain condition now in a second step, it multiply total quantity with respective price (column C) in the given array (C3 to C8) and gives sum of product.