2

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?

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)

3
  • Good suggestion, I sadly only use array functions rarely... Still, this solution would have to be mixed mith mine though, as it does not solve the problem of excluding the first row... – prom85 Feb 4 '16 at 8:26
  • I tried it and it does work well, BUT it does not ignore text cells... Therefore, I get the #VALUE error as well... – prom85 Feb 4 '16 at 8:29
  • Perfect, now it is readable AND working. Much better than my solution. Thanks – prom85 Feb 4 '16 at 8:39
0

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.

1
  • Did not know that. I still prefer a solution where all necessary actions are done in the cell directly... – prom85 Feb 4 '16 at 8:38
0

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...

2
  • what is "WAHR" in your formula? – Máté Juhász Feb 4 '16 at 8:25
  • 1
    Sorry, forgot to translate this word... Excel uses local function names.. WAHR is TRUE in german... – prom85 Feb 4 '16 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.