Sumif (Year)

[Mike 215]

Hi,
I need to sum a colunm based on the date the cell entered.

A1 has a year, 2009
A5:A10000 has Dates, 01/02/2009
E5:E10000 Data to sum
E1 the sum of all Column E entered in 2009

Thanks for any help.

 

[Bob Phillips]

Try

=SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000)

HTH

Bob

 

[Mike 215]

Try

=SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000)

HTH

Bob








- Show quoted text -

Hi Bob,
I did a copy & paste and I typed it in but it is not working, it gives
a 0 answer.
I am running Office 2003 on XP. Does this matter?

Mike

 

[David Biddulph]

I think that Bob may have intended to say not
=SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000)
but
=SUMPRODUCT(--(YEAR(A5:A1000)=A1),E5:E1000)
as in his formula the double unary minus would have done nothing.

 

[Mike 215]

I think that Bob may have intended to say not
=SUMPRODUCT(--(YEAR(A5:A1000))=A1,E5:E1000)
but
=SUMPRODUCT(--(YEAR(A5:A1000)=A1),E5:E1000)
as in his formula the double unary minus would have done nothing.
--
David Biddulph






- Show quoted text -

David,
Your formula works great.
I have always been able to get great help here.

Thank You
Mike