Subtraction with multiple ISBLANKS inside IF statements

[beklein]

Hi all,

I need to subtract some values form a base value only if another cell is filled. Because this has to be done for multiple cells, I now have the working forumla

=H3-IF(ISBLANK(A3);0;E3)-IF(ISBLANK(A4);0;E4)-IF(ISBLANK(A5);0;E5)-IF(ISBLANK(A6);0;E6) continued to cell 13.

This is not a good looking formula, and I have the feeling it should be possible to make it (a lot) shorter, but I can't figure it out. I've tried it in an array formula as

=H3-IF((ISBLANK(A3:A16));0;E3:E16)

But this only appears to work for the cells in row 3.

I was wondering if anyone knows a way to make this formula more efficient. Thanks in advance.

Cheers, Bart

 

[Scott Huish]

=H3-SUM(IF(A3:A16<>"",E3:E16))

Confirm with CTRL-SHIFT-ENTER instead of just Enter.

 

[Fluff]

Try
=H3-SUMPRODUCT((ISBLANK(A3:A6)=FALSE)*(E3:E6))

 

[beklein]

Try
=H3-SUMPRODUCT((ISBLANK(A3:A6)=FALSE)*(E3:E6))

That works perfectly, thanks a bunch! I haven't quite figured out how it works exactly, but I'll look into that Got a nice cleaned up formula now

 

[Fluff]

Not sure if you noticed Scott's formula which should work as well.
But glad we could help & thanks for the feedback

 

[beklein]

=H3-SUM(IF(A3:A16<>"",E3:E16))

Confirm with CTRL-SHIFT-ENTER instead of just Enter.

Just tried this solution as well, works as a charm, thanks!