Aggregate weekly data by by another condition from daily data

**keelanmj** · 11-18-2010, 01:57 PM

I am new to this forum so please forgive me... I do think this question will add good information to the community.

The title may be a bit confusing, but here is my problem:

I have marketing data by channel by day and I would like to aggregate it by week by channel. Column B has the Dates, Column C has the channels, and Column D has the revenue data. Each day there is a revenue number associated with each channel.

I have attached an example file with the daily data and the weekly output format. I assume that using a combination of if and sumif or a sumproduct would work but I do not have much experience with sumproduct. This problem is giving me trouble because I want to sum data based on 2 conditions. I know a pivot table is designed to solve this problem but I would like to avoid this because I will have many different data sets that I will have to organize every week.

I have searched all over and have not found a similar problem with the daily data organized like mine.

Any help would be greatly appreciated. Thanks!

**DonkeyOte** · 11-18-2010, 03:04 PM

As you say you should be using a Pivot Table to do this - I confess I don't really understand the point as to why this would be an issue ?

If you want to use formulae then given use of XL2007+ use SUMIFS in pref. to SUMPRODUCT

**keelanmj** · 11-18-2010, 05:01 PM

I do not want to use pivot tables because this process applies to multiple groups of data. This requires me to very manually update data and group days into weeks for each of 11+ pivot tables on a weekly and monthly basis. I am just trying to minimize the upkeep needed to have good data in a "dashboard" excel file for a bunch of groups of data.

**DonkeyOte** · 11-19-2010, 06:26 AM

Presumably you've already considered using Dynamic Named Ranges as the source for the Pivots and utilising the Pivot Tables in built Grouping functionality?
for lots of good information re: the points outlined above and other Pivot related info see various Pivot Links at: http://www.contextures.com/tiptech.html

However, if you're truly desperate to re-invent the wheel and use formulae to ape Pivot Table functionality see SUMIFS function as previously mentioned.