Help with sum of column

Posted by DMH on December 11, 2001 6:55 PM

I have three columns in a worksheet A=Date, B=Purchases of ItemB per Day and C=Purchases of ItemC per 7 Days. I have created a summary the lists the purchases of ItemB and ItemC for the last 7 days, last 30 days and last 90 days(counting bottom up). I was able to do this using the offset function, countA and named ranges for the daily data for ItemB. However, the weekly data for ItemC is giving me fits because I have 6 blank cells between each ItemC datapoint. I have tried using an IF ISNUMBER combo but my syntax and logic seems to have reached their limits.

Can someone suggest a direction. I am not comfortable with macros or vba if there is a function that will do.

Thanks a bunch!

Posted by Bariloche on December 11, 2001 7:25 PM

DMH,

You want to use a pivot table for this. Trust me on this. I know you're recoiling in horror at the thought (LOL) but bear with me. (I just did it with some dummy data, its gorgeous!)

First select your three columns of data, including the headers (your column names, like Date, PuchaseB, PurchaseC). Now click on Data > Pivot Table report and step through the wizard. I don't know which version of Excel you're using so post back if you can't figure it out.

Assuming you do get into the "layout" portion of the wizard, you want to use "Date" as your row field and "PurchB" and "PurchC" as data fields.

Once Excel creates the pivot table, right click on the "Date" tile (as I call that object) and select "Group and Outline" > "Group ...". Chose Days and spin the little spinner button up to 7. Now click Ok and your data should be grouped (summed) by each 7 day period.

You might have noticed when you were in the Grouping dialog box that you can also chose to group by months, quarters, and years. Just change the grouping parameters for your other periods. Or, you can copy the pivot table (hold the Ctrl key while dragging the sheet tab) and reconfigure the copies with the different grouping levels. Or, you can put them all in one pivot table. Whatever you like.

You'll really like this. Give it a try. (Its painless, really, and a lot easier than what you've been doing.) :-))

have fun