Excel SUMPRODUCT - exclude header cells from formula

Question

I have two columns, one with a date and one with values, and I want to sum up all cells, where the year of the date is the desired year. My problem is, that I have headers in the table, and I don't want to manually define first and last rows to consider, but to take all rows...

This fails with #VALUE! error:

=SUMPRODUCT((YEAR(C:C)=2015)*(E:E))

This works:

 =SUMPRODUCT((YEAR(C2:C1000)=2015)*(E2:E1000))

In C1 and E1 I have headers (text). They are formatted as text and in simple formulas that's enough to have them excluded from the formula, but this is not working with the SUMPRODUCT...

Is there a better solution?

Answer 1

One other, slightly more readable, suggestion.

=SUM(IFERROR((YEAR(C:C)=2015)*E:E,0))

Use that as an array function (Press Ctrl + Shift + Enter, not just Enter)

Answer 2

Is there a better solution?

Yes, there is.

Use tables (select your range and then: Insert - Table), and there you can write your formula like:

=SUMPRODUCT((YEAR(Table1[HeaderC])=2015)*(Table1[HeaderE]))

Of course, instead of HeaderC write the real header in column C, and the same for column E.

Answer 3

Just after posting it I had an idea and it works:

Solution:

=SUMPRODUCT((YEAR(INDIRECT("C2:C"&(MATCH(0;E:E;-1));TRUE))=2015)*(INDIRECT("E2:E"&(MATCH(0;E:E;-1));TRUE)))

The match function finds the last cell with a value >= 0, for me that's working fine. Dependent on the use case, you would have to adjsut the match function...