Summing monthly data from weekly data input, with a twist

[Phoenix599]

I'm hoping an Excel wizard can help me... I know enough about Excel to be
dangerous, but this issue is giving me a migraine.

I have weekly data organized by row with the first cell of the row
indicating the week (i.e. Jan 3, 2008). Subsequent week dates are 7 days
apart through the remainder of the year.

The trick is...I want to be able to sum a month's data and display it in a
"Current Month" column, based on the current date, so that only the current
month's data displays. For example, in July 2008, only the sum of available
data for July will display. Once August 1st arrives, then August's data
displays in the "Current Month" column.

Any help would be greatly appreciated!

 

[Bob Phillips]

=SUMPRODUCT(--(YEAR(A2:A200)=YEAR(TODAY())),--(MONTH(A2:A200)=MONTH(TODAY())),B2:B200)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[J Sedoff]

Do you want a running sum, or a total?

1st Method
-------------------------------------
This will get you the absolute total for the entire month for each row
=SUMIF($B$1:$B$39,MONTH(A1),$C$1:$C$39)

To set this up, have the date in Column A, the month of the date in Column B
-- that's just =Month(A1) --, the 'value' in Column C. (I put the total in
Column D, but you can obviously put it anywhere.)

Date Month Value Month's Total
1/1/2008 1 1 19
1/2/2008 1 2 19
1/3/2008 1 4 19
1/4/2008 1 5 19
1/5/2008 1 1 19
1/31/2008 1 6 19
2/1/2008 2 10 37
2/2/2008 2 12 37
2/3/2008 2 15 37

2nd Method
----------------------------------------------
To get a running total for the month, use this:
=SUMPRODUCT(--($A$1:$A$9<=A1),--($B$1:$B$9=MONTH(A1)),$C$1:$C$9)

It checks that the date is less than or equal to the current row (first
condition)--thus a running sum--and that the month in Column B matches the
current row (second condition)--thus a sum for only the current month.

Date Month Value Month's Total
1/1/2008 1 2 2
1/2/2008 1 2 4
1/3/2008 1 4 8
1/4/2008 1 5 13
1/5/2008 1 1 14
1/31/2008 1 6 20
2/1/2008 2 10 10
2/2/2008 2 12 22
2/3/2008 2 15 37

Hope this helps, Jim

 

[Phoenix599]

Thank you very much!

=SUMPRODUCT(--(YEAR(A2:A200)=YEAR(TODAY())),--(MONTH(A2:A200)=MONTH(TODAY())),B2:B200)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Phoenix599]

Thank you very much!