SUM based on multiple conditions - SORRY, URGENT!!!

[Guest]

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

 

[CLR]

You might use a helper column to CONCATENATE your conditions into a single
cell,
like 2005-02-Widget-John, and then use a regular SUMIF on that column and
your sales amount column to get the results you're after.............

Vaya con Dios,
Chuck, CABGx3

 

[Biff]

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),--
(D1100=I1),E1:E100)

Now, all you have to do is select the conditions you're
interested in from the dropdowns.

Biff

 

[Darren Hill]

You could also use SumProduct as described on this page:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Let's say the ranges containing the criteria (years, month, product,
salesperson) are A1:A10, B1:B10, C1:C10, D110,
=SUMPRODUCT((A1:A10="2003")*(B1:B10="June")*(C1:C10="Widget")*(D110="Fred")*(E1:E10))

You can replace the "2003" etc with cell references, to more easily handle
things:
=SUMPRODUCT((A1:A10=A50)*(B1:B10=B50)*(C1:C10=C50)*(D110=D50)*(E1:E10))

Darren

 

[Guest]

I had the same problem a few weeks ago. I tried arrays but it took to long
to open the sheet. I ended up using DSUM. It is a lot easier to use and
edit. Here a link to the tutorial that I used.

http://support.microsoft.com/default.aspx?scid=kb;en-us;282851