How to SUM multiple {SUM(SUMIFs)} array formulas? (SUM an array of arrays)

[Mr Mongolia]

This is my first thread so go easy on me..

I want to add multiple SUM(SUMIFS(...)) array formulas, without having to manually type SUM(SUMIFS(...))+SUM(SUMIFS(...))+ etc

Is there a way to SUM an array of array formulas?

----------------------------------------
My specific example:

	A	B	C	D
1	DATE:	01/12/2014
2	CURRENCY:	USD	GBP	EUR
3	COUNTRY:	United Kingdom	Overseas
4
5	DATE	CURRENCY	COUNTRY	AMOUNT
6	01/12/2014	USD	Overseas	100
7	01/12/2014	GBP	Overseas	50
8	01/12/2014	USD	Overseas	45
9	01/12/2014	EUR	United Kingdom	98
10	02/12/2014	GBP	Overseas	67
11	02/12/2014	EUR	United Kingdom	101
12	02/12/2014	USD	United Kingdom	73
13	02/12/2014	USD	Overseas	20
14	02/12/2014	GBP	Overseas	107
15	03/12/2014	GBP	Overseas	55
16	03/12/2014	GBP	United Kingdom	42
17	03/12/2014	EUR	United Kingdom	89

<tbody>
</tbody>

The array formula I currently have is;

{=SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2))}

i.e. the SUM of each SUMIFS based on Date and Currency - in this instance all currencies on the 01/12/14 = 293 (100+50+45+98).

I now need to include a third criteria in my SUMIFS to be based on the Country, but if I add;

{=SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2,C5:C16,B3:C3))}

It only SUMs the SUMIFS for USD and United Kingdom + GBP and Overseas giving me 50 instead of the total 293.

I realise for this example both formulas give the same answer, but I need the flexibility in this formula to be able to apply it to many different data sets, as the criteria lists are set to change dynamically depending on several other factors.

I could just type;

{=SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2,C5:C16,B3))+SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2,C5:C16,C3))}

But when I have over 50 different countries this will be extremely time consuming.

Is there a better way?

Thanks

 

[Jonmo1]

When doing multi criteria array sum(sumifs..

And you need 2 sets of multi critera, those 2 sets of criteria must be transposed of each other. (1 a column, the other a row)

{=SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2,C5:C16,B3:B4))}
or
{=SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2,C5:C16,TRANSPOSE(B3:C3)))}

Also, you can change SUM to SUMPRODUCT so you don't have to enter as CSE
=SUMPRODUCT(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2,C5:C16,TRANSPOSE(B3:C3)))

 

[gsistek]

The best way to handle this would be to use a pivot table.

 

[Mr Mongolia]

Transpose it... so simple! Yes that works perfectly, thank you Jonmo1!

(Although I don't think the SUMPRODUCT works with the transposed array - CSE works fine though)

Out of interest, is there a way of doing this with a third array criteria?

 

[Jonmo1]

Not that I'm aware of.
It might be time to start using helper columns (they're not a bad thing)
Or use a pivot table as suggested by gsiestek

For the helper column method, you could do something like this based on your original formula with just 1 criteria array
{=SUM(SUMIFS(D5:D16,A5:A16,B1,B5:B16,B2:D2))}

in say G5 and filled down, put
=ISNUMBER(MATCH(B5,B$2:D$2,0))

Then use the regular sumifs (non array entered)
SUMIFS(D5:D16,A5:A16,B1,G5:G16,TRUE)