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