sum based on date in excel
 

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

Hi

try

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

Cheers
JulieD

"koskyil" wrote in message
...
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...

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

Karen Kosky

"JulieD" wrote:

Hi

try

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

Cheers
JulieD

"koskyil" wrote in message
...
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...

sum based on date in excel
 

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


"JulieD" wrote:

Hi

try

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

Cheers
JulieD

"koskyil" wrote in message
...
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...

sum based on date in excel
 

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/xlFunctio...tml#SumProduct
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Munro" wrote in message
...
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.. :)


"JulieD" wrote:

Hi

try

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

Cheers
JulieD

"koskyil" wrote in message
...
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...