sum based on date in excel

[koskyil]

I'm trying to sum a list of expenditures based on the year in which they were
spent. I've put together the following function:
=SUMIF('Grant Project 1'!N18:W18,((YEAR(DATEVALUE('Grant Project
1'!N18:W18)))=2005),'Grant Project 1'!N17:W17)

where GrantProject1!N18:W18 are the dates that the expenditures occurred (in
date number format, MM/DD/YY)
and GrantProject1!N17:W17 are the expenditures.

This function is working, but returning zero - when it shouldn't. Any ideas?

Thanks much...

 

[JulieD]

Hi

try

=SUMPRODUCT(--(YEAR('Grant Project 1'!N18:W18)=2005)*'Grant Project
1'!N17:W17)

Cheers
JulieD

 

[koskyil]

Julie:
This worked like a charm. Thanks so much for the quick response!

Karen Kosky

 

[Munro]

Thankyou from me too JulieD.

It took about 90 minutes but your post was the final step in solving my
problem. I was trying to sum values from each row in a resource forecast
table from a particular month (usually the current month but it's
changeable). I was frustrated that I couldn't just put a >AK4 in a SUMIF
function but thanks to your help I used the following formula instead:
=SUMPRODUCT(($M$6:$AK$6>=$AK$4)*M7:AK7)
(the month headers are on row 6 and AK4 is the current month)

I didn't know what the -- were for in your formula but I deleted them and it
still works..

 

[Bernard Liengme]

One of these will work
=SUMPRODUCT(($M$6:$AK$6>=$AK$4)*M7:AK7)
=SUMPRODUCT(--($M$6:$AK$6>=$AK$4),M7:AK7)

In the second one the purpose of -- (double negation) is to convert Boolean
FALSE/TRUE to numbers 0/1
For more details on SUMPRODUCT
Bob Phillips:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish:
http://www.contextures.com/xlFunctions04.html#SumProduct
best wishes