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

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 :)

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



"marika1981" wrote in 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 :)

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 :)
.

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, D1:D10,
=SUMPRODUCT((A1:A10="2003")*(B1:B10="June")*(C1:C1 0="Widget")*(D1:D10="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) *(D1:D10=D50)*(E1:E10))

Darren

On Thu, 17 Feb 2005 19:39:25 -0500, CLR wrote:

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



"marika1981" wrote in 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 :)





--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

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...b;en-us;282851

"marika1981" wrote:

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 :)