Alternate for Array Sum Formula

[tabbymulla]

Can somebody please suggest an alternate to this array formula so it can calculate way faster

{=SUM(
IF(
($B$2:$B$10=$C15)*
($C$2:$C$10=$C$13)*
($D$2:$D$10=D$14)>0,
IF(
$G$2:$G$10<>"",
$G$2:$G$10,
IF(
$F$2:$F$10<>"",
$F$2:$F$10,
$E$2:$E$10))))}

I have tried below which reduces the calculation time to 1/3 but it is too much typing for the large data I am dealing with

=SUMIFS(
$G$2:$G$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"<>"&"")
+SUMIFS(
$F$2:$F$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"="&"",
$F$2:$F$10,"<>"&"")
+SUMIFS(
$E$2:$E$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"="&"",
$F$2:$F$10,"="&"")

 

[Gerald Higgins]

A very small simplification which should work the same . . .

You can replace
"="&""
with just
""

And you can replace
"< >"&""
with
"< >"

 

[Eric W]

Welcome to the forum.

In addition to Gerald's idea, you can remove the "<>" ranges and conditions because if the range is empty, it will contribute 0 to the total. So with those changes, your formula is:

=SUMIFS(
$G$2:$G$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14)
+SUMIFS(
$F$2:$F$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"")
+SUMIFS(
$E$2:$E$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"",
$F$2:$F$10,"")

Next, if I read your formula correctly, you want to pick the rightmost value from the F:H columns that matches your conditions. I've tried several options, but this seems easiest. Add a helper column in H (you can put it elsewhere if you want) with this formula:

H2: =IFERROR(LOOKUP(2^999,E2:G2),0)

drag down the column. Then your SUMIFS would only be:

=SUMIFS($H$2:$H$10,$B$2:$B$10,$C15,$C$2:$C$10,$C$13,$D$2:$D$10,H$14)

Hope this helps.

 

[tabbymulla]

	Column1	Column2	Column3	Column4	Column5	Column6
	A	AA	AAA	100	95	92
	A	AA	AAA	85	83	81
	A	AA	BBB	200	199	160
	A	BB	AAA	65	55	49
	B	AA	AAA	89	88	83
	B	AA	BBB	150	149	145
	B	BB	AAA	140	135
	B	BB	BBB	190	185
	B	AA	AAA	510
		AA
			AAA	BBB
		A	173	160
		B	593	145

<tbody>
</tbody>

Forumula is used in D15 with red font.

 

[tabbymulla]

Welcome to the forum.

In addition to Gerald's idea, you can remove the "<>" ranges and conditions because if the range is empty, it will contribute 0 to the total. So with those changes, your formula is:

=SUMIFS(
$G$2:$G$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14)
+SUMIFS(
$F$2:$F$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"")
+SUMIFS(
$E$2:$E$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"",
$F$2:$F$10,"")

Next, if I read your formula correctly, you want to pick the rightmost value from the F:H columns that matches your conditions. I've tried several options, but this seems easiest. Add a helper column in H (you can put it elsewhere if you want) with this formula:

H2: =IFERROR(LOOKUP(2^999,E2:G2),0)

drag down the column. Then your SUMIFS would only be:

=SUMIFS($H$2:$H$10,$B$2:$B$10,$C15,$C$2:$C$10,$C$13,$D$2:$D$10,H$14)

Hope this helps.

Honestly, I am surprised that you understood my intention correctly without any reference data based which I was only able to post below later. However, detailed explanation is also posted below:

Basically i want the sum of "Column 6" for the given criteria but the data in Column 6 can only be entered after some delay w.r.t. Column 1, Column 2, Column 3 & Column 4.

Till Column 6 data is entered, i want excel to use the number available in Column 5 which is also entered after some delay w.r.t. Column 1, Column 2, Column 3 & Column 4 but before Column 6.
And till Column 5 data is entered, i want excel to use the number available in Column 4.
Now I am familiar with two SUM/IF arrangements as shown in first post.
First one is array sum/if arrangement which is convenient to write but results in terribly long calculation time with 1.5 seconds for just one column and I have over 100 columns in one sheet and about 9 sheets.
Second one is using SUMIFS which requires extensive time to write but relatively better calculation time of 0.5 seconds for column but is still quite high.
Now I need to do away with the array arrangement but doing so will take quite some time and I want to know if there is any better/other arrangement.
Just let me know other arrangement which can get the required result and I will check the arrangement for calculation timing. If the other arrangement is also convenient to write than that is a plus.


I will check the helper column arrangement for getting the correct result and calculation timing today. But as per my experience, SUMIFS can only check for criteria in one dimension. I mean if I use a helper column which will identify the column to be used for sum range, specifying a criteria with column range while other criteria are with row ranges is not possible with SUMIFS. Please correct me if I am wrong and how to do it as I faced such requirement in past but couldn't get SUMIFS to do it.

 

[Eric W]

Given your layout, here's how the helper column would look:

Book1
	A	B	C	D	E	F	G	H
1		Column1	Column2	Column3	Column4	Column5	Column6	Helper Column
2		A	AA	AAA	100	95	92	92
3		A	AA	AAA	85	83	81	81
4		A	AA	BBB	200	199	160	160
5		A	BB	AAA	65	55	49	49
6		B	AA	AAA	89	88	83	83
7		B	AA	BBB	150	149	145	145
8		B	BB	AAA	140	135		135
9		B	BB	BBB	190	185		185
10		B	AA	AAA	510			510
11
12
13			AA
14				AAA	BBB
15			A	173	160
16			B	593	145
Sheet1

Cell Formulas
Range	Formula
D15	=SUMIFS($H$2:$H$10,$B$2:$B$10,$C15,$C$2:$C$10,$C$13,$D$2:$D$10,D$14)

True, SUMIFS only works in one dimension, but you can use other tricks to compress the second dimension into 1, which is what the helper column does. And you can possibly use other functions, such as SUMPRODUCT to work in multiple dimensions. For example, I noticed that the values in E:F:G are always decreasing. If that's always true, we can just pick the minimum value from that range. This formula works without a helper column:

D15: =SUMPRODUCT(--($B$2:$B$10=$C15),--($C$2:$C$10=$C$13),--($D$2:$D$10=D$14),SUBTOTAL(5,OFFSET($E$2:$G$2,ROW($E$2:$E$10)-ROW($E$2),0)))

However, I suspect it may be slower than the helper column method. And of course, if the strictly decreasing rule isn't true, then it won't work right.

Let us know how your timing tests work, and if there's anything else about your data that might provide a way to improve on these ideas.

 

[Eric W]

Forgot to post the H2 formula:

=IFERROR(LOOKUP(2^999,E2:G2),"")

 

[tabbymulla]

Given your layout, here's how the helper column would look:

	A	B	C	D	E	F	G	H
1		Column1	Column2	Column3	Column4	Column5	Column6	Helper Column
2		A	AA	AAA	100	95	92	92
3		A	AA	AAA	85	83	81	81
4		A	AA	BBB	200	199	160	160
5		A	BB	AAA	65	55	49	49
6		B	AA	AAA	89	88	83	83
7		B	AA	BBB	150	149	145	145
8		B	BB	AAA	140	135		135
9		B	BB	BBB	190	185		185
10		B	AA	AAA	510			510
11
12
13			AA
14				AAA	BBB
15			A	173	160
16			B	593	145

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D15 =SUMIFS($H$2:$H$10,$B$2:$B$10,$C15,$C$2:$C$10,$C$13,$D$2:$D$10,D$14) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

True, SUMIFS only works in one dimension, but you can use other tricks to compress the second dimension into 1, which is what the helper column does. And you can possibly use other functions, such as SUMPRODUCT to work in multiple dimensions. For example, I noticed that the values in E:F:G are always decreasing. If that's always true, we can just pick the minimum value from that range. This formula works without a helper column:

D15: =SUMPRODUCT(--($B$2:$B$10=$C15),--($C$2:$C$10=$C$13),--($D$2:$D$10=D$14),SUBTOTAL(5,OFFSET($E$2:$G$2,ROW($E$2:$E$10)-ROW($E$2),0)))

However, I suspect it may be slower than the helper column method. And of course, if the strictly decreasing rule isn't true, then it won't work right.

Let us know how your timing tests work, and if there's anything else about your data that might provide a way to improve on these ideas.

Strictly decreasing rule isn't true for my actual data and the relevant data columns of criteria ranges and sum ranges are not adjacent at all.

So I used the helper column with IF statement. For sample data I shared, helper column formula in H2 is

=IF(G2<>"",G2,IF(F2<>"",F2,E2))

The calculation time for SUMIFS with helper column arrangement was average 0.14 seconds (0.12 seconds for SUMIFS formula column and 0.02 seconds for Helper column) in comparison to average 1.5 seconds for the array sum/if formula and average 0.5 seconds for SUMIFS formula I mentioned in my original post for the same column's calculation.

This is a big improvement and seems it can be bettered. Although adding helper columns for my data would be cumbersome but it will be a one time activity.

Thanks Eric for your expert advice and quick response.

 

[jasonb75]

Maybe a pivottable with the helper column would further improve performance?

 

[tabbymulla]

Maybe a pivottable with the helper column would further improve performance?

Good idea. The reason i didn't use pivot table earlier was that it would not sum the most right number. Now with helper column it seems the right thing to do. Although I face some problems while referencing to pivot table cells in drag formulas but i guess its time to get into that as well.