arrays - Alternate for Array Sum Formula

Question

Table copied as Text

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     

and some more explanation

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 included below in 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.

This is my table:

And I want to add the right most columns which are not empty i.e. have a number in it, but with the criteria for the first three columns in cell D15.

I only found option to add image. Please let me know how to upload excel file. enter image description here

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,"="&"")

标签： arraysexcel

Answer 1

If you're OK with using a helper column (which you should be), you can use this formula in a helper cell and drag down. (In my example at bottom, this formula is in cell H2 and drag down.)

= INDEX(E2:G2,MATCH(-1E+300,E2:G2,-1))

This gets all of the data in either column 4 5 or 6 all into one column.

Then you can use a simpler SUMIFS formula in cell D15:

= SUMIFS($H$2:$H$10,         // Sum range (helper column)
         $B$2:$B$10,$C15,    // Criteria 1 (A or B)
         $C$2:$C$10,$C$13,   // Criteria 2 (AA or BB)
         $D$2:$D$10,D$14)    // Criteria 3 (AAA or BBB)

See below, working example:

---

DISCLAIMER

This answer will simplify your formulas, but I'm not sure if this will help with the performance problems you are experiencing. SUMIFS in itself I don't see being likely the cause of long calculation times. Probably you are experiencing long calculation times because other parts of your spreadsheet are using inefficient formulas and/or formulas involving volatile cells, but that is just a guess because I have no idea what the rest of your spreadsheet looks like.