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

 

[shyguy44]

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)

same result 0 for every cell

There arent any gaps but there are different years 01-jan-09 all up to 03-sep-11 although it wont be affected as I tried your formula which only goes to Row 10

Date
03-Jan-09 2.49 0.36 Jan-09 0
08-Jan-09 13.92 0.00 Feb09 0
05-Feb-09 0.99 0.00 Mar-09 0
08-Feb-09 9.45 0.00 Apr09 0
14-Mar-09 4.95 0.00 May-09 0
18-Apr-09 0.99 0.00 Jun09 0
22-Apr-09 9.45 0.00 Jul-09 0
05-May-09 0.99 0.00 Aug09 0
08-May-09 1.89 0.00 Sep-09 0

 

[Aladin Akyurek]

See post #10.

 

[T. Valko]

same result 0 for every cell

There arent any gaps but there are different years 01-jan-09 all up to 03-sep-11 although it wont be affected as I tried your formula which only goes to Row 10

Date
03-Jan-09 2.49 0.36 Jan-09 0
08-Jan-09 13.92 0.00 Feb09 0
05-Feb-09 0.99 0.00 Mar-09 0
08-Feb-09 9.45 0.00 Apr09 0
14-Mar-09 4.95 0.00 May-09 0
18-Apr-09 0.99 0.00 Jun09 0
22-Apr-09 9.45 0.00 Jul-09 0
05-May-09 0.99 0.00 Aug09 0
08-May-09 1.89 0.00 Sep-09 0

Ok, then you have to include the year as part of the condition.

You can either build that test condition into the formula or use a cell to hold the condition.

If you don't want to use a cell that contains the text string Jan2009 then you'd have to enter the true Excel date and format the cell to display only the month name.

Then you'd use something like this...

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

Where H2 contains any date in January 2009. It's better to use the 1st of the month date.

 

[shyguy44]

Ok, then you have to include the year as part of the condition.

You can either build that test condition into the formula or use a cell to hold the condition.

If you don't want to use a cell that contains the text string Jan2009 then you'd have to enter the true Excel date and format the cell to display only the month name.

Then you'd use something like this...

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

Where H2 contains any date in January 2009. It's better to use the 1st of the month date.

Thanks Biff

Was able just to enter jan 09 feb 09 and copy down, and even though shows 01-01-209 in formula bar it works ok

 

[shyguy44]

See post #10.

sorry only just tried post 10, although it didnt work for either option

 

[Aladin Akyurek]

sorry only just tried post 10, although it didnt work for either option

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

will work when:

H2 is

03-Jan-09

or

1/3/09

or

1-jan-09

or

30 Jan 09

or

'09 Jan 09

or

1-Jan-2009

That is, It will work as long as H2 can be interpreted as (true) date by Excel...

 

[shyguy44]

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

will work when:

H2 is

03-Jan-09

or

1/3/09

or

1-jan-09

or

30 Jan 09

or

'09 Jan 09

or

1-Jan-2009

That is, It will work as long as H2 can be interpreted as (true) date by Excel...

when i paste and copy your formula directly it just copies as if ' was enter before i.e.text
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=TEXT($H2,"mmmyy"),$C$2:$C$100)

then when i edit the formula to delete the = then re-enter it it says formula contains an error.

Im using excel 2003 if that makes any difference

 

[T. Valko]

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

That formula is missing a closing parenthesis.

Try it like this...

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

 

[Aladin Akyurek]

when i paste and copy your formula directly it just copies as if ' was enter before i.e.text
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mmmyy")=TEXT($H2,"mmmyy"),$C$2:$C$100)

then when i edit the formula to delete the = then re-enter it it says formula contains an error.

Im using excel 2003 if that makes any difference

Sorry about that... Missing a paren:

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

 

[shyguy44]

Thank you to both, much appreciated