Hi!
Create data validation dropdown lists for your data
conditions: year, month, product, salesperson.
Assume your data is in the range A1:E100 with column E
being the sales amount.
The dropdown lists are in the following cells:
F1 = year
G1 = month
H1 = product
I1 = salesperson
Use a formula like this:
=SUMPRODUCT(--(A1:A100=F1),--(B1:B100=G1),--(C1:C100=H1),--
(D1:D100=I1),E1:E100)
Now, all you have to do is select the conditions you're
interested in from the dropdowns.
Biff
-----Original Message-----
I'm setting up a spreadsheet that will summarize data
based on multiple
(though simple) conditions.
Specifically, the base data will include several columns -
year, month,
product, salesperson - and then a sales amount. I'm
trying to set up a
series of formulae that will sum the sales amount, only
if desired conditions
are met in the first four columns ( i.e. exact year,
exact month, exact
product and exact salesperson).
The only way I've found to do this successfully is using
an ARRAY FORMULA.
This concerns me because the workbook will contain
hundreds of these formulae
(covering every possible permutation) and I'd like it to
update easily.
ARRAY FORMULAE seem difficult to update, copy, paste and
edit.
Is it easy to update a large number of array formulae
automatically, or is
there another way to sum data based on multiple
conditions, as described
above?
MANY, MANY, MANY THANKS!!!
Marika :)
.
|