Summing dates

[shyguy44]

Not sure if best to use a sum if or sumproduct. I have excel 2003

In column A I have a list of dates in the format 2-Jan-2009 all the way up to 3-Sep-11

In column C i have a cost and in column D i have a return

I need a formula to calculate when jan09 and subsequent dates feb09 mar09 etc appears in column A then what the cost in column C will be and the return in column D

then a formula for jan 09 to appear and the sum of column D minus the sum of Column C to give the net

Thanks

 

[Aladin Akyurek]

Not sure if best to use a sum if or sumproduct. I have excel 2003

In column A I have a list of dates in the format 2-Jan-2009 all the way up to 3-Sep-11

In column C i have a cost and in column D i have a return

I need a formula to calculate when jan09 and subsequent dates feb09 mar09 etc appears in column A then what the cost in column C will be and the return in column D

then a formula for jan 09 to appear and the sum of column D minus the sum of Column C to give the net

Thanks

Try...

Either:

Control+shift+enter, not just enter...

=SUM(IF(TEXT($A$2:$A$100,"mmmyy")="Jan09",$D$2:$D$100-$C$2:$C$100))

Or:

Just enter...

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")="Jan09"),$D$2:$D$100-$C$2:$C$100)

 

[T. Valko]

Not sure if best to use a sum if or sumproduct. I have excel 2003

In column A I have a list of dates in the format 2-Jan-2009 all the way up to 3-Sep-11

In column C i have a cost and in column D i have a return

I need a formula to calculate when jan09 and subsequent dates feb09 mar09 etc appears in column A then what the cost in column C will be and the return in column D

then a formula for jan 09 to appear and the sum of column D minus the sum of Column C to give the net

Thanks

Try these...

For the cost:

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")="Jan2009"),C2:C10)

For the return:

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")="Jan2009"),D2:D10)

For the net:

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")="Jan2009"),D2:D10-C2:C10)

Adjust the ranges to suit.

 

[shyguy44]

Thanks guys, instead of using text in the formula is is possible to use a cell value,

i.e. I have a table at starting in cell H2 which just says jan then h3 feb etc. I can amend them to say Jan09 Feb09 Mar09 etc although when I amend the formula to point to the cell it doesnt work

e.g i changed the formula

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")="Jan2009"),C2:C10)

to

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")=H2,C2:C10))

 

[Aladin Akyurek]

Thanks guys, instead of using text in the formula is is possible to use a cell value,

i.e. I have a table at starting in cell H2 which just says jan then h3 feb etc. I can amend them to say Jan09 Feb09 Mar09 etc although when I amend the formula to point to the cell it doesnt work

e.g i changed the formula

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")="Jan2009"),C2:C10)

to

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")=H2,C2:C10))

Better to include the year test... Therefore:

H2: Jan09
H3: Feb09

etc.

I2, copy down:

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=H2),$C$2:$C$100)

J2, copy down:

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")="Jan09"),$D$2:$D$100)

K2, copy down:

=J2-I2

 

[shyguy44]

Better to include the year test... Therefore:

H2: Jan09
H3: Feb09

etc.

I2, copy down:

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=H2),$C$2:$C$100)

J2, copy down:

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")="Jan09"),$D$2:$D$100)

K2, copy down:

=J2-I2

how should H2 H3 etc be formatted as the result is 0

I formatted as mmm-yy and mmmyy and general same result each time whenver =H2 H3 is used using formula below

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=H2),$C$2:$C$100)

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")="Jan09"),$D$2:$D$100) works fine

 

[Aladin Akyurek]

how should H2 H3 etc be formatted as the result is 0

I formatted as mmm-yy and mmmyy and general same result each time whenver =H2 H3 is used using formula below

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=H2),$C$2:$C$100)

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")="Jan09"),$D$2:$D$100) works fine

In H2 just enter the string Jan09, not a date.

If you want to enter there a true date, replace H2 in the formula with:

TEXT(H2,"mmmyy")

 

[T. Valko]

Thanks guys, instead of using text in the formula is is possible to use a cell value,

i.e. I have a table at starting in cell H2 which just says jan then h3 feb etc. I can amend them to say Jan09 Feb09 Mar09 etc although when I amend the formula to point to the cell it doesnt work

e.g i changed the formula

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")="Jan2009"),C2:C10)

to

=SUMPRODUCT(--(TEXT(A2:A10,"mmmyyyy")=H2,C2:C10))

Are your dates all within the same year?

Are there any empty cells within the date range? Empty cells will evaluate as month January.

Try it like this...

=SUMPRODUCT(--(TEXT(A$2:A$10,"mmm")=H2),C$2:C$10)

 

[shyguy44]

In H2 just enter the string Jan09, not a date.

If you want to enter there a true date, replace H2 in the formula with:

TEXT(H2,"mmmyy")

I did just enter as Jan09 Feb09 Mar09 etc it autoformatted it as mmm-yy then when i look in the formula bar it says 01-01-2009 for jan09

 

[Aladin Akyurek]

I did just enter as Jan09 Feb09 Mar09 etc it autoformatted it as mmm-yy then when i look in the formula bar it says 01-01-2009 for jan09

I see. You can prevent the date interpretation by using a quote like this:

'Jan09

If this is too involved, enter the following in H2 downwards:

1-Jan-09
1-Feb-09

etc. Then invoke:

I2:

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=TEXT($H2,"mmmyy"),$C$2:$C$100)

J2:

=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=TEXT($H2,"mmmyy"),$D$2:$D$100)

K2:

=J2-I2