I need a formula...

[Alan Smith]

Hi all,

Here's the scenario - I have a file I use for sales forecasts, laid out as
follows:

Client Name - G6:G709
Product Name - UB5:AAM5 (12 groups of repeating names)
Data to be added - UB6:AAM709

I need a formula for a summary sheet that can calculate the revenue per
product name per client for the year (so adding the 12 periods), and that
will also work when the source data is filtered, if possible.

Thanks for any help & suggestions.

Cheers,

Alan

 

[Don]

First, I would look into pivot tables. I like them for analysis , but a bit
tricky to work into monthly production stuff.

I would look into the "sumif" statement if you are looking for totals. On
the summary sheet , put the name you want to sum. From your example , you
may want to concatinate "product name & client " (I would have one colum to
go after , but sumif can do mult)

Sumif(Range to look and compair , thing you want to compair , range to sum
up and total)

 

[Alan Smith]

Thanks Don, but unfortunately the data layout is not conducive to using a
pivot table, and sumif won't work with multiple criteria. Sumifs returns
#VALUE errors, maybe I'm using it incorrectly? The formula with the SUMIFS is:

=SUMIFS('2008-2011 Revenue'!UB6:AAM709,'2008-2011 Revenue'!UB4:AAM4,'Client
Summary'!B3,'2008-2011 Revenue'!G6:G709,'Client Summary'!A6)

Where UB6:AAM709 is the data to be summed, UB4:AAM4 is the 12 groups of
criteria, and G6:G709 is the client list. Anybody spot an error in there?

Alan

 

[Herbert Seidenberg]

Maybe the manual steps in this
reverse Pivot Table approach can be
automated with VBA.
http://www.freefilehosting.net/download/3akjb