Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 :) |
#2
|
|||
|
|||
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 :) |
#3
|
|||
|
|||
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 :) . |
#4
|
|||
|
|||
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/ |
#5
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Sum If Multiple Conditions | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Count Based upon Multiple Conditions | Excel Worksheet Functions | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions |