SUMIF when YEAR is correct - Problem with date formats!

[Tom Muller]

Hello folks,

I have the following table:


A B C D E
1 Jan 01 Feb 01 Jan 02 Feb 02
2 Costs 50E 50E 60E 60E
3 Cashflows 100E 20E 100E

And I want to do the following:
The values in the row 2 should be calculated automatically.
- Excel should look in row 1 and read the YEARS.
- Then excel should go in row 3 and SUM all values of the same YEAR, e.g. of
year 02 (which would be 120E)
-Then excel should devide this SUM in equal parts and spread it over the
cells in row 2 of the same YEAR (here: 60E each for Jan 02 and Feb 02)

I have tried e.g. the following:
B2: =SUMIF(B1:E1;"=YEAR(B1)";B3:E3)/2

But that won't work. There is a problem understanding the year! In the cells
(e.g. "Jan 01" the date is stored like that 01.01.01).

So if I change everythin to e.g.

A B C D E
1 2001 2001 2002 2002
2 Costs 50E 50E 60E 60E
3 Cashflows 100E 20E 100E

and
B2: =SUMIF(B1:E1;B1;B3:E3)/2
everything works fine.

But I don't want to enter in the cells of row No.1 only year numbers, but
Month & Years.

Can anybody help me on how I have to make the formula correct?

Thanks for any advice!

Ciao
Tom

 

[Peo Sjoblom]

If they are real dates use

=SUMPRODUCT(--(YEAR(B1:E1)=2002),B3:E3)

 

[Tom Muller]

Hello Peo,

If they are real dates use

=SUMPRODUCT(--(YEAR(B1:E1)=2002),B3:E3)

Super, that worked!!

Thank you!

Greetings,
Tom