# Array formula help

#### JackDanIce

##### Well-known Member
Hi,

Have a 5 column data table, columns 1-3 string values, columns 4-5 numerical values and each row is a unique record.

e.g.
a, b, c, 100, 4
d, e, f, 200, 20

On another sheet, I use two SUMIFS formula's to return values from column 4 and column 5 into (e.g.) columns A and B

(1)
Rich (BB code):
``=SUMIFS(Data!\$P\$5:\$P\$146,Data!\$M\$5:\$M\$146,N1,Data!\$N\$5:\$N\$146,O1,Data!\$O\$5:\$O\$146,P1)``
(2)
Rich (BB code):
``=SUMIFS(Data!\$Q\$5:\$Q\$146,Data!\$M\$5:\$M\$146,N1,Data!\$N\$5:\$N\$146,O1,Data!\$O\$5:\$O\$146,P1)``
I'd like a formula like:=[SUM(1)-SUM(2)]/SUM(1), without helper columns or showing formula (2) results anywhere (no hidden column)

I tried an array formula but result is not correct:
Rich (BB code):
``={(SUMIFS(Data!\$P\$5:\$P\$146,Data!\$M\$5:\$M\$146,S1:S4,Data!\$N\$5:\$N\$146,T1:T4,Data!\$O\$5:\$O\$146,U1:U4)-SUMIFS(Data!\$Q\$5:\$Q\$146,Data!\$M\$5:\$M\$146,S1:S4,Data!\$N\$5:\$N\$146,T1:T4,Data!\$O\$5:\$O\$146,U1:U4))/SUMIFS(Data!\$P\$5:\$P\$146,Data!\$M\$5:\$M\$146,S1:S4,Data!\$N\$5:\$N\$146,T1:T4,Data!\$O\$5:\$O\$146,U1:U4)}``
Can someone suggest suitable formula?

TIA,
Jack

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

##### Well-known Member
VBA is needed for jagged arrays, Excel can only return one level from the worksheet. A pivot table calculated field is better than formulas here:

#### JackDanIce

##### Well-known Member
Hi, thanks for the suggestion, however, each triplet (Group 1 & Group 2 & Group 3) is unique in the LHS table (in your example cols A:E, i.e. you wouldn't have row 17 and row 11)

There is a single data table with unique rows.

There is an input sheet where 3 variables (per row) that match against the data table and returns a value
Duplicate rows in the input sheet can exist, but must return the unique value from the data table that the 3 variables evaluate to.

Each triplet of variables returns 2 values, I want to sum each column of returned value and calculate the percentage totals of the two columns.

Triplet1 = 100 and 50
Triplet2 = 200 and 40
Triplet3 = 100 and 50

Output required (400 + 140) / 400

The suggested formula looks similar to what I tried, except for wanting a range of criteria, rather than single cell (e.g. H2 vs H13:H17):
Code:
``````=(SUMIFS(Data!\$P\$5:\$P\$146,Data!\$M\$5:\$M\$146,Input!D13:D17,Data!\$N\$5:\$N\$146,Input!E13:E17,Data!\$O\$5:\$O\$146,Input!F13:F17)-

SUMIFS(Data!\$Q\$5:\$Q\$146,Data!\$M\$5:\$M\$146,Input!D13:D17,Data!\$N\$5:\$N\$146,Input!E13:E17,Data!\$O\$5:\$O\$146,Input!F13:F17))/

SUMIFS(Data!\$P\$5:\$P\$146,Data!\$M\$5:\$M\$146,Input!D13:D17,Data!\$N\$5:\$N\$146,Input!E13:E17,Data!\$O\$5:\$O\$146,Input!F13:F17)``````
But output is not correct

Last edited:

##### Well-known Member
I'm not sure sumifs/countifs can accept array criteria, but here's what did:

Even if there is a shorter SUMIFS formula I prefer helper columns, VBA, or a pivot table with VBA here.

#### JackDanIce

##### Well-known Member
I can't use a helper column or VBA or pivot in this instance, that's the conditions placed by the task masters!

Worse comes worse, I will use a helper column but it's very sensitive data which they want the user to not see so trying to find non VBA/non Pivot solution.

I'll check the SUMPRODUCT suggestion shortly, thanks for helping and continuing to @sheetspread

#### steve the fish

##### Well-known Member
Are you wanting 4 sumifs formulas added together? eg in your first sumifs you want criteria S1,T1,U1 then S2,T2,U2 etc? If so use sumproduct(sumifs(etc.

##### Well-known Member
use sumproduct(sumifs(etc.
I thought sum/countifs crtieria might allow more than 1 array but it didn't seem to work here, I'll try again.

Last edited:

##### Well-known Member
According to Dave Bruns you can include up to 2 criteria (the last has to be a row/column pair). The above example requires 3:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### JackDanIce

##### Well-known Member
Morning, I ended up using a helper column in the lookup data to create distinct keys and using VLOOKUP to retrieve required data.

Array formula that now works (\$A\$1 contains last used row number of sheet, within INDIRECT):
Rich (BB code):
``````{=IFERROR((

SUMPRODUCT(SUMIFS(Data!\$R\$5:\$R\$146,Data!\$O\$5:\$O\$146,INDIRECT("\$D\$14:\$D\$"&\$A\$1),Data!\$P\$5:\$P\$146,INDIRECT("\$E\$14:\$E\$"&\$A\$1),Data!\$Q\$5:\$Q\$146,INDIRECT("\$F\$14:\$F\$"&\$A\$1)),L14:L16)-

SUMPRODUCT(SUMIFS(Data!\$S\$5:\$S\$146,Data!\$O\$5:\$O\$146,INDIRECT("\$D\$14:\$D\$"&\$A\$1),Data!\$P\$5:\$P\$146,INDIRECT("\$E\$14:\$E\$"&\$A\$1),Data!\$Q\$5:\$Q\$146,INDIRECT("\$F\$14:\$F\$"&\$A\$1)),L14:L16))/

SUMPRODUCT(SUMIFS(Data!\$R\$5:\$R\$146,Data!\$O\$5:\$O\$146,INDIRECT("\$D\$14:\$D\$"&\$A\$1),Data!\$P\$5:\$P\$146,INDIRECT("\$E\$14:\$E\$"&\$A\$1),Data!\$Q\$5:\$Q\$146,INDIRECT("\$F\$14:\$F\$"&\$A\$1)),L14:L16), "")}``````
Using spacing to show it's still (A-B)/A

I also discovered applying OFFSET to a named ranges (2D table) to isolate a single column returns #VALUE if used within an array formula, so this would error:
Rich (BB code):
``{=IFERROR((SUMPRODUCT(SUMIFS(OFFSET(Table_Rates,,5,1),OFFSET(Table_Rates,,2,1),INDIRECT("\$D\$14:\$D\$"&\$A\$1),OFFSET(Table_Rates,,3,1),INDIRECT("\$E\$14:\$E\$"&\$A\$1),OFFSET(Table_Rates,,4,1),INDIRECT("\$F\$14:\$F\$"&\$A\$1)),L14:L16)... etc}``

Last edited:

Nice