Summing data from days to Months realtime

Hi,

I'm totally stuck at an impass and I'm hoping someone can help me out.

I've got a sheet that updates daily from the web and applies a new row of data that is datestampted with the day-month-year.

I need a formula that will recognize all the data from the same month, then sum it for use in a chart.

For a gold star, as new months are entered, new rows should be created to accomodate them.


I've attached a test sheet with what I'm looking for. There is a WEB_SHEET sheet which is where the data is that I refresh from each day, and a CHART_DATA sheet which is where I store the data for the chart from.

Thank you,
JNic

You can use the worksheet function MONTH to display the integer value of the month.

Example: Assume that cell A2 has the value of Aug-08 in it. Using the formula MONTH(A2) will return the value 8. August is the eighth month in the calendar. This also works if the date format is a standard date such as 8/28/2008


Armed with this knowledge you can then use a macro to summarize all of the entries for the month. Use a CASE statement to tally up the totals for each month then display in the appropriate column.

Thanks TB!

I did some research on the CASE statement, but I couldn't figure out how to get it to do what I want. I did, however, find some avenues from there that lead me to a solution.

I used an IF statemtent to determine whether or not there was a date stamp in any cell in A:A.

=IF(A5>0, MONTH(A5)&YEAR(A5),"")

If there is, I used the MONTH statement you told me about, and took a swing in the dark and tried YEAR (it worked too) and converted that to a month/year value. eg 82008

Then I used a SUMIF statement to sum all the data from that month:

=SUMIF(K:K, 82008,B:B )

I placed all the years of data I have in a new sheet, and now, in REALTIME, as new data is entered, it automatically updates the chart!

NO MORE RUNNING REPORTS!!!
HUZZAH FOR REALTIME!

Thanks,
JN