Theory Post: Array Formula Troubleshooting

[The_Snook]

Afternoon all,

I've written an array formula that isn't returning what I'd expect and I'm struggling to get my head around why. The following is an example of the structure of my formula:

{=SUM(SUMIFS(TableName[FieldW], TableName[FieldX], NamedRange(1 Cell), TableName[FieldY], NamedRange(4 Cells), TableName[FieldZ], NamedRange(3 Cells)))}

Are there any obvious flaws in my logic?

UPDATE: I've added an example.

Thanks in advance,

Snook

[FDibbins]

Hard to say without seeing it in some context and some sample data

[JohnTopley]

Your post suggests the named ranges are different sizes: with SUMIFS all ranges must be the same size.

and no sure why SUM (SUMIFS .....)?

[The_Snook]

Hi JohnTopley/FDibbins,

I've attached an example of my query, I'd be grateful if you could point out where I'm going wrong (I think JohnTopley is already onto it).

Thanks,

Snook

[FDibbins]

THis seems to work...
=SUM(SUMIFS(Table1[[#All],[FieldX]],Table1[[#All],[FieldY]],FY,Table1[[#All],[FieldY2]],Dept,Table1[[#All],[FieldZ]],Status))
CSE
yours (to compare)
=SUM(SUMIFS(TableName[FieldW], TableName[FieldX], NamedRange(1 Cell), TableName[FieldY], NamedRange(4 Cells), TableName[FieldZ], NamedRange(3 Cells)))}

[The_Snook]

I'm still getting the same output value (3370).

[Jonmo1]

When you have 2 multi criteria ranges (Dept and Status) in the Sum(Sumif structure
They must be transposed of each other
1 a Single Row range like A1:E1
The other a single Column Range like A1:A5

I suggest changing Status Named Range to a row reference, say G9:I9, and populate those values in those cells.

[Jonmo1]

OR transpose the range within the formula itself
=SUM(SUMIFS(Table1[FieldX],Table1[FieldY],FY,Table1[FieldY2],Dept,Table1[FieldZ],TRANSPOSE(Status2)))

Also FYI
If you change SUM to SUMPRODUCT, the formula doesn't need to be entered as an array.

[The_Snook]

Jonmo1 you've nailed it mate and also taught me something new!

I'd have been staring at that for hours!

Thanks again!

Snook

[Jonmo1]

You're welcome.

[The_Snook]

Out of interest what would happen if I had 3 multi criteria ranges?

[Jonmo1]

I am unaware of any method to do 3 or more multi criteria ranges with the sum(sumifs syntax

You would have to do
sumifs+sumifs+sumifs

[The_Snook]

Cool, thankfully I'm not in that boat, I was just curious.

[The_Snook]

For info - Transposing the range within the formula itself only works when the formula is entered as an array, e.g. {=SUM(SUMIFS.....

When you change the SUM to SUMPRODUCT and press enter it returns 0.