Sum with multiple If statements

[mf1]

Hello,

I have created this formula within a table and works as expected.

I have converted it to range, as the formula was too long. Is there a better or shorter formula? (I played around with sumproduct, however wasn't able to achieve same result)

=SUM(IF(Master!$H11=0,Master!$F11,0)+IF(Master!$K11=0,Master!$I11,0)+IF(Master!$N11=0,Master!$L11,0)+IF(Master!$Q11=0,Master!$O11,0)+IF(Master!$T11=0,Master!$R11,0))

Cheers,

Mick

 

[Hui]

Can you post the workbook that it is used in so we have some real data

 

[mf1]

Hui,

Please find attached.

Cheers

 

[NARAYANK991]

Hi ,

I have not seen your workbook , but this might do the job :

=SUM(IF(N(OFFSET(Master!F11:R11,,{2,5,8,11,14}))=0,N(OFFSET(Master!F11:R11,,{0,3,6,9,12}))))

Narayan

 

[jeffreyweir]

A SUMPRODUCT wouldn't work unless you used OFFSET and wrapped the OFFSET in an N function. So you'd have something like this:

=SUMPRODUCT(N(OFFSET(F13,0,{0,3,6,9,12}))*(N(OFFSET(H13,0,{0,3,6,9,12}))=0))

...which is no more efficient or better than your existing formula.

 

[jeffreyweir]

Ha! Snap, Narayan!

 

[jeffreyweir]

Here's a simpler, more efficient one:
=E5*ISBLANK(G5)+H5*ISBLANK(J5)+K5*ISBLANK(M5)+N5*ISBLANK(P5)+Q5*ISBLANK(S5)+T5*ISBLANK(V5)+W5*ISBLANK(G5)+Z5*ISBLANK(AB5)+AC5*ISBLANK(G5)

 

[Somendra Misra]

You can also try below formula: check for the result.

=SUMPRODUCT($E3:$AC3*ISNUMBER(SEARCH("*Receipt*",$G$2:$AE$2))*($G3:$AE3=""))

Regards,

 

[jeffreyweir]

Also, you can use your original formula without the SUM. Is there a right formula to use in this case? Depends on who’s trying to comprehend what’s happening here in 6 months time. I'd use your original formula, without the SUM

 

[mf1]

Thankyou for your help, much appreciated.

 

[Haseeb A]

Or SUMIFS,

=SUMIFS(E3:AC3,G3:AE3,"",G$2:AE$2,"*receipt*")