How to do the equivalent of a SUMPRODUCTIF, a formula that doesn’t exist in Excel.
Here’s the detail on how.
SUMPRODUCT is a lesser-known function, and sums a set of products. (a1*b1*c1) +(a2*b2*c2) + […] + (an*bn*cn)
SUMIF sums a range of cells if a certain condition is true. For example, sum people’s salaries in a column if they are based in London.
I wanted a way of putting a condition on the SUMPRODUCT function. That is, I wanted to do a sum of the products only where a condition for those rows held true.
To make the formulae more manageable, let’s assume I’ve named a few ranges:
conditions is the column of data that I want to validate the condition against
range1 and range2 are the two ranges that I want to do the conditional SUMPRODUCT on.
And let’s assume I only want to do this if the values in the conditions range are “London”.
The formula would read:
=SUMPRODUCT(--(conditions="London"),range1,range2)
Basically, the first term acts as a range in its own right, taking the value of 1 (for London) and 0 (for anything else). This means that it’s not actually doing a conditional SUMPRODUCT, but instead it’s multiplying the entries for which the condition fails by zero, which has the same effect. The double minus at the beginning is to ensure that the first argument is read as a formula.
Source: http://www.wizardofexcel.com/2011/05/09/sumproductif/