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
 

sheetspread

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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Group1</td><td style=";">Group2</td><td style=";">Group3</td><td style=";">Series1</td><td style=";">Series2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">ELO</td><td style=";">TDC</td><td style=";">KQN</td><td style="text-align: right;;">100</td><td style="text-align: right;;">87</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">0.547038328</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">AWU</td><td style=";">AFK</td><td style=";">GRD</td><td style="text-align: right;;">20</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">LXH</td><td style=";">RFH</td><td style=";">TGH</td><td style="text-align: right;;">13</td><td style="text-align: right;;">77</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Group1</td><td style=";">Group2</td><td style=";">Group3</td><td style=";">Sum of Series1</td><td style=";">Sum of Series2</td><td style=";">Sum of Field1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">SDY</td><td style=";">SKX</td><td style=";">LTX</td><td style="text-align: right;;">9</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">287</td><td style="text-align: right;;">130</td><td style="text-align: right;;">0.547038328</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">GJB</td><td style=";">C</td><td style=";">NBH</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">74</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">BAY</td><td style=";">LYK</td><td style=";">KUX</td><td style="text-align: right;;">92</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">KVF</td><td style=";">OMN</td><td style=";">EJY</td><td style="text-align: right;;">33</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">FZD</td><td style=";">TPR</td><td style=";">KSS</td><td style="text-align: right;;">4</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">53</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">DQL</td><td style=";">TQR</td><td style=";">GXG</td><td style="text-align: right;;">91</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">LD</td><td style=";">MQM</td><td style=";">JSC</td><td style="text-align: right;;">93</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">HCF</td><td style=";">EIF</td><td style=";">HJA</td><td style="text-align: right;;">21</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">GCH</td><td style=";">MGT</td><td style=";">CQI</td><td style="text-align: right;;">53</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">KJB</td><td style=";">CFE</td><td style=";">NJC</td><td style="text-align: right;;">76</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">70</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">GXC</td><td style=";">UZW</td><td style=";">EVC</td><td style="text-align: right;;">48</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">KIQ</td><td style=";">NWF</td><td style=";">FHW</td><td style="text-align: right;;">9</td><td style="text-align: right;;">97</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">JFP</td><td style=";">EDN</td><td style=";">FTT</td><td style="text-align: right;;">15</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">RNS</td><td style=";">ITL</td><td style=";">QHP</td><td style="text-align: right;;">37</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">86</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">NHJ</td><td style=";">FLL</td><td style=";">EX</td><td style="text-align: right;;">32</td><td style="text-align: right;;">91</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">QFB</td><td style=";">KKM</td><td style=";">EAM</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=(<font color="Blue">SUMIFS(<font color="Red">$D$2:$D$24,$A$2:$A$24,H2,$B$2:$B$24,I2,$C$2:$C$24,J2</font>)-SUMIFS(<font color="Red">$E$2:$E$24,$A$2:$A$24,H2,$B$2:$B$24,I2,$C$2:$C$24,J2</font>)</font>)/SUMIFS(<font color="Blue">$D$2:$D$24,$A$2:$A$24,H2,$B$2:$B$24,I2,$C$2:$C$24,J2</font>)</td></tr></tbody></table></td></tr></table><br />
 

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:

sheetspread

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Group1</td><td style=";">Group2</td><td style=";">Group3</td><td style=";">Series1</td><td style=";">Series2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Silver</td><td style=";">Left</td><td style=";">Oxygen</td><td style="text-align: right;;">100</td><td style="text-align: right;;">87</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Violet</td><td style=";">East</td><td style=";">Neon</td><td style="text-align: right;;">20</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Red</td><td style=";">Left</td><td style=";">Lithium</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #00B0F0;;">Red</td><td style="background-color: #00B0F0;;">West</td><td style="background-color: #00B0F0;;">Beryllium</td><td style="text-align: right;background-color: #00B0F0;;">13</td><td style="text-align: right;background-color: #00B0F0;;">77</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Orange</td><td style=";">Up</td><td style=";">Carbon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Indigo</td><td style=";">West</td><td style=";">Lithium</td><td style="text-align: right;;">9</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Violet</td><td style=";">West</td><td style=";">Beryllium</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Black</td><td style=";">South</td><td style=";">Carbon</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Silver</td><td style=";">East</td><td style=";">Fluorine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Red</td><td style=";">There</td><td style=";">Fluorine</td><td style="text-align: right;;">92</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Violet</td><td style=";">South</td><td style=";">Carbon</td><td style="text-align: right;;">33</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Yellow</td><td style=";">Here</td><td style=";">Carbon</td><td style="text-align: right;;">4</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Red</td><td style=";">Right</td><td style=";">Neon</td><td style="text-align: right;;">53</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">White</td><td style=";">Here</td><td style=";">Helium</td><td style="text-align: right;;">91</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Violet</td><td style=";">There</td><td style=";">Oxygen</td><td style="text-align: right;;">93</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Violet</td><td style=";">Here</td><td style=";">Oxygen</td><td style="text-align: right;;">21</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="background-color: #00B0F0;;">Violet</td><td style="background-color: #00B0F0;;">Up</td><td style="background-color: #00B0F0;;">Lithium</td><td style="text-align: right;background-color: #00B0F0;;">53</td><td style="text-align: right;background-color: #00B0F0;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Red</td><td style=";">There</td><td style=";">Neon</td><td style="text-align: right;;">76</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #00B050;;">-0.18182</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Orange</td><td style=";">North</td><td style=";">Neon</td><td style="text-align: right;;">48</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Green</td><td style=";">Right</td><td style=";">Carbon</td><td style="text-align: right;;">9</td><td style="text-align: right;;">97</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #00B050;;">-0.18182</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">White</td><td style=";">North</td><td style=";">Neon</td><td style="text-align: right;;">15</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Blue</td><td style=";">North</td><td style=";">Fluorine</td><td style="text-align: right;;">37</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Yellow</td><td style=";">Down</td><td style=";">Boron</td><td style="text-align: right;;">32</td><td style="text-align: right;;">91</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9 (3)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I15</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">D4+D14</font>)-(<font color="Red">E4+E14</font>)</font>)/(<font color="Blue">D4+D14</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I17</th><td style="text-align:left">=(<font color="Blue">SUMPRODUCT(<font color="Red">$D$2:$D$20,COUNTIF(<font color="Green">$H$3:$H$6,$A$2:$A$20</font>),COUNTIF(<font color="Green">$I$3:$I$6,$B$2:$B$20</font>),COUNTIF(<font color="Green">$J$3:$J$6,$C$2:$C$20</font>)</font>)-SUMPRODUCT(<font color="Red">$E$2:$E$20,COUNTIF(<font color="Green">$H$3:$H$6,$A$2:$A$20</font>),COUNTIF(<font color="Green">$I$3:$I$6,$B$2:$B$20</font>),COUNTIF(<font color="Green">$J$3:$J$6,$C$2:$C$20</font>)</font>)</font>)/SUMPRODUCT(<font color="Blue">$D$2:$D$20,COUNTIF(<font color="Red">$H$3:$H$6,$A$2:$A$20</font>),COUNTIF(<font color="Red">$I$3:$I$6,$B$2:$B$20</font>),COUNTIF(<font color="Red">$J$3:$J$6,$C$2:$C$20</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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.
 

sheetspread

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>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Group1</td><td style=";">Group2</td><td style=";">Group3</td><td style=";">Series1</td><td style=";">Series2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="background-color: #00B0F0;;">Silver</td><td style="background-color: #00B0F0;;">Left</td><td style=";">Oxygen</td><td style="text-align: right;background-color: #00B0F0;;">100</td><td style="text-align: right;;">87</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Group1</td><td style=";">Group2</td><td style=";">Group3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #00B0F0;;">Violet</td><td style="background-color: #00B0F0;;">East</td><td style=";">Neon</td><td style="text-align: right;background-color: #00B0F0;;">20</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Red</td><td style=";">Left</td><td style=";">Lithium</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #00B0F0;;">Red</td><td style="background-color: #00B0F0;;">West</td><td style=";">Beryllium</td><td style="text-align: right;background-color: #00B0F0;;">13</td><td style="text-align: right;;">77</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Orange</td><td style=";">Up</td><td style=";">Carbon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Indigo</td><td style=";">West</td><td style=";">Lithium</td><td style="text-align: right;;">9</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Violet</td><td style=";">West</td><td style=";">Beryllium</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Black</td><td style=";">South</td><td style=";">Carbon</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Silver</td><td style=";">East</td><td style=";">Fluorine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Red</td><td style=";">There</td><td style=";">Fluorine</td><td style="text-align: right;;">92</td><td style="text-align: right;;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Violet</td><td style=";">South</td><td style=";">Carbon</td><td style="text-align: right;;">33</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Silver</td><td style=";">Here</td><td style=";">Carbon</td><td style="text-align: right;;">4</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">186</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Red</td><td style=";">Right</td><td style=";">Neon</td><td style="text-align: right;;">53</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">White</td><td style=";">Here</td><td style=";">Helium</td><td style="text-align: right;;">91</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">186</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Violet</td><td style=";">There</td><td style=";">Oxygen</td><td style="text-align: right;;">93</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Violet</td><td style=";">Here</td><td style=";">Oxygen</td><td style="text-align: right;;">21</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="background-color: #00B0F0;;">Violet</td><td style="background-color: #00B0F0;;">Up</td><td style=";">Lithium</td><td style="text-align: right;background-color: #00B0F0;;">53</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Red</td><td style=";">There</td><td style=";">Neon</td><td style="text-align: right;;">76</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Orange</td><td style=";">North</td><td style=";">Neon</td><td style="text-align: right;;">48</td><td style="text-align: right;;">43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Green</td><td style=";">Right</td><td style=";">Carbon</td><td style="text-align: right;;">9</td><td style="text-align: right;;">97</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">White</td><td style=";">North</td><td style=";">Neon</td><td style="text-align: right;;">15</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Blue</td><td style=";">North</td><td style=";">Fluorine</td><td style="text-align: right;;">37</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Yellow</td><td style=";">Down</td><td style=";">Boron</td><td style="text-align: right;;">32</td><td style="text-align: right;;">91</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9 (4)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H9</th><td style="text-align:left">=D2+D3+D4+D14</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H11</th><td style="text-align:left">{=SUM(<font color="Blue">SUMIFS(<font color="Red">D2:D20,A2:A20,H3:H6,B2:B20,TRANSPOSE(<font color="Green">I3:I6</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<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:

Some videos you may like

This Week's Hot Topics

Top