As mentioned a few weeks ago, I recently found out how to do the equivalent of a SUMPRODUCTIF. Here's the detail on how.
First of all, some context.
SUM does exactly what it says on the tin. It sums a range of cells.
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. I think the double minus at the beginning is to ensure that the first argument is read as a formula.
Quite beautiful.
