Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array formula - sum with multiple criteria
Hi Kelly,
You might like to try using the awesome power of the SUMPRODUCT function. You may never use SUMIF or Database functions ever again! The basic idea of SUMPRODUCT is fairly straightforward: e.g. SUMPRODUCT({1,2,3},{4,5,6}) = (1 * 4) + (2 * 5) + (3 * 6) = 32. However it becomes really powerful when you realise that TRUE and FALSE have values of 1 and 0 and therefore can be used to filter out values from a sum. For example, in a simplification of your case: SUMPRODUCT(('SalesDetail'!A3:A5="China")*('SalesDe tail'!F3:F5="Q1")*('SalesDetail'!G3:G5)) (where in this case the G column is the actual sales figure) This might evaluate to: SUMPRODUCT(({China, China, Australia}="China")*({Q1, Q2, Q2}="Q1")*({5000,10000,20000})) = SUMPRODUCT({TRUE, TRUE, FALSE}*{TRUE, FALSE, FALSE}*{5000,10000,20000}) and since TRUE = 1 and FALSE = 0, this goes to SUMPRODUCT({1 * 1 * 5000, 1 * 0 * 10000, 0 *0 * 20000}) = SUMPRODUCT({5000, 0,0}) = 5000 So you have just easily calculated all the Q1 sales for China. And you can use as many "=" statements as you need and instead of "China" make a reference to a nearby cell. That way, you can have easily make up a whole table of values like this: Q1 Q2 Q3 Q4 .... China Australia England ..... and in each cell of this table you'd have a SUMPRODUCT function that references a row and column and so you can easily analyse all your data just by creating a SUMPRODUCT in one cell and dragging it across and down! Hope this helps. - Atreides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria for lookup array | Excel Worksheet Functions | |||
MODE fx in array using multiple criteria | Excel Worksheet Functions | |||
Can I use an array formula with multiple criteria in the same row? | Excel Worksheet Functions | |||
SUMIF MULTIPLE ARRAY CRITERIA | Excel Worksheet Functions | |||
Array Formula w/ Multiple SumIf Criteria | Excel Worksheet Functions |