Alternate for Array Sum Formula

Question

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

Answer 1

I feel sorry that I don' get your real purpose at first. 

My solution is similar to your second formula, remove some unnecessary part. "" won't affect the result of sumif.

I suggest you calculate separately then add together.

SUM G=SUMIFS($G$2:$G$10,$B$2:$B$10,$C$15,$C$2:$C$10,$D$13,$D$2:$D$10,$D$14)

SUM F=SUMIFS($F$2:$F$10,$B$2:$B$10,$C$15,$C$2:$C$10,$D$13,$D$2:$D$10,$D$14)

SUM E=SUMIFS($E$2:$E$10,$B$2:$B$10,$C$15,$C$2:$C$10,$D$13,$D$2:$D$10,$D$14)

Then result =SUM(I2,J2,K2)

Answer 2

Hi,

I suggest you using AND function in nest IF. I haven't tested following formula by my side, just write it directly. 

=IF(And($B$2:$B$10=$C15)*($C$2:$C$10=$C$13)*($D$2:$D$10=D$14)>0, $G$2:$G$10<>""),

SUM($G$2:$G$10),

IF(And($B$2:$B$10=$C15)*($C$2:$C$10=$C$13)*($D$2:$D$10=D$14)>0, $F$2:$F$10<>""),

SUM($F$2:$F$10),

IF(And($B$2:$B$10=$C15)*($C$2:$C$10=$C$13)*($D$2:$D$10=D$14)>0,$G$2:$G$10="", $F$2:$F$10=""),

SUM($E$2:$E$10),

"NO VALUE"))

This is not working. I cant get the correct result with above formula. I tried with some adjustment and also using array (CSE) with below but still no result.

=IF(AND($B$2:$B$10=$C15,$C$2:$C$10=$C$13,$D$2:$D$10=K$14),

SUM($G$2:$G$10),

IF(AND($B$2:$B$10=$C15,$C$2:$C$10=$C$13,$D$2:$D$10=K$14,$G$2:$G$10=""),

SUM($F$2:$F$10),

IF(AND($B$2:$B$10=$C15,$C$2:$C$10=$C$13,$D$2:$D$10=K$14,$G$2:$G$10="",$F$2:$F$10=""),

SUM($E$2:$E$10),

0)))

Answer 3

I found the image option. Please also let me know how to upload the excel file. The above formula is used in cell D15.

I want to add the right most cell which is not empty i.e. has number in it, with criteria for first three columns.

Answer 4

Hi,

I suggest you using AND function in nest IF. I haven't tested following formula by my side, just write it directly. 

=IF(And($B$2:$B$10=$C15)*($C$2:$C$10=$C$13)*($D$2:$D$10=D$14)>0, $G$2:$G$10<>""),

SUM($G$2:$G$10),

IF(And($B$2:$B$10=$C15)*($C$2:$C$10=$C$13)*($D$2:$D$10=D$14)>0, $F$2:$F$10<>""),

SUM($F$2:$F$10),

IF(And($B$2:$B$10=$C15)*($C$2:$C$10=$C$13)*($D$2:$D$10=D$14)>0,$G$2:$G$10="", $F$2:$F$10=""),

SUM($E$2:$E$10),

"NO VALUE"))

Answer 5

Hi

Better post a screenshot of your data (showing rows and columns)

Or better yet 

Share a copy of your file here 

So we could find a right solution to your problem