Data Advanced Criteria

**Aland2929** · 07-16-2010, 12:55 PM

I want to extract three products to an output range and summarise them by month as per the attached sample file. The data in my output range is showing the data for both channels, but I want to add the two channels together for each product by month.
Thank you in advance

**Aland2929** · 07-17-2010, 03:13 AM

Originally Posted by Aland2929

I want to extract three products to an output range and summarise them by month as per the attached sample file. The data in my output range is showing the data for both channels, but I want to add the two channels together for each product by month.
Thank you in advance

In Lotus you could enter a formula in the Output Range. Is there a similar solution in Excel?

**TMS** · 07-17-2010, 05:04 AM

You could do this with a SUMPRODUCT formula.

If your products are listed in cells Q2 to S2, the formula in cell Q3 would be:

Please Login or Register  to view this content.

for January/AU

Fill down and across for the rest of the month/product Budget combinations.

You'd need to repeat the formulae for the Actuals

**Aland2929** · 07-17-2010, 07:09 AM

Originally Posted by TMShucks

You could do this with a SUMPRODUCT formula.

If your products are listed in cells Q2 to S2, the formula in cell Q3 would be:

Please Login or Register  to view this content.

for January/AU

Fill down and across for the rest of the month/product Budget combinations.

You'd need to repeat the formulae for the Actuals

Thanks for your reply and effort. The formula works but requires quite a bit of re-arranging in my model. Is there a way I can populate columns M & N directly with the results I am looking for?
Thank You

**shg** · 07-17-2010, 11:56 AM

You can do this with Advanced Filter. Moved your data down several rows and put the criteria above the data. It's well explained in Help.

Please stop quoting whole posts. It's just clutter.

**TMS** · 07-17-2010, 07:19 PM

You know what, given what you're trying to do, I'd be inclined to use a Pivot Table. Much more flexible and scaleable (IMO).

The following code will take your raw data on sheet 1 ( Data ) and create a Pivot Table in a new work sheet.

Please Login or Register  to view this content.

Regards

**Aland2929** · 07-19-2010, 05:35 AM

Thanks very much for your time and effort TMShucks. SHG, I would appreciate if you could post the appropriate criteria to display the sum of "like" products for both channels as I have not been able to figure it out? In my model I am getting January to June twice, but only want it once by adding the "like" products for each channel?
Thank You in advance