Explanation

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an elegant and versatile function, which this example illustrates nicely.

In this example, SUMPRODUCT is configured with two arrays . The first array is the range that holds product pricing:

$C$5:$C$9

Note the reference is absolute to prevent changes as the formula is copied to the right. This range evaluates to the following array:

{99;69;129;119;49}

The second array is generated with this expression:

--(D5:D9="x")

The result of D5:D9=“x” is an array of TRUE FALSE values like this:

{TRUE;TRUE;FALSE;FALSE;FALSE}

The double negative (–) converts these TRUE FALSE values to 1s and 0s:

{1;1;0;0;0}

So, inside SUMPRODUCT we have:

=SUMPRODUCT({99;69;129;119;49},{1;1;0;0;0})

The SUMPRODUCT function then multiplies corresponding items in each array together:

=SUMPRODUCT({99;69;0;0;0})

and returns the sum of products, 168 in this case.

Effectively, the second array acts as a filter for the values in the first array. Zeros in array2 cancel out items in array1, and 1s in array2 allow values from array1 to pass through into the final result.

With a single array

SUMPRODUCT is set up to accept multiple arrays, but you can simplify this formula a bit by providing a single array at the start:

=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))

The math operation (multiplication) automatically coerces the TRUE FALSE values in the second expression to ones and zeros, with no need for a double negative.

Explanation

The first thing this formula does is check the date in column D against the date in the header (E4:J4).

=IF($D5<=E$4

Translated: if the date in column D is less than or equal to the date in row E.

Note these are mixed references . The column in $D5 is locked, and the row in E$4 is locked. This allows the formula to be copied across the table.

If the result of this test is TRUE, the IF function returns the expense from column C, otherwise IF returns zero (0).

=IF($D5<=E$4,$C5,0)

Again note the reference to $C5 is has the column locked, so the expense is always picked up from column C.

As the formula is copied across the table, expenses begin on the correct month. In months where the expense is not yet valid, the result is zero.

With boolean logic

Using boolean logic , the formula could be re-written like as below, for a simpler formula:

=$C5*($D5<=E$4)

Here the logical expression is used to “cancel out” expenses in months where they have not yet started.