Improve this formula, combo of sum, Sumifs and Sumproduct

**nickmax1** · 08-29-2014, 11:35 AM

I have a formula below

=SUBTOTAL(9,$H$18:$H$190)+SUMPRODUCT(SUBTOTAL(9,OFFSET($G$18,ROW($G$18:$G$190)-ROW($G$18),0)),($H$18:$H$190="")+0,($J$18:$J$190<>"Red")+0)+SUBTOTAL(9,$D$193:$D$300)

What this does is sum colomn H (when filtered), adds the sum of colomn G as long as there is no number in Col H next to it and the colomn J hasnt got the word "Red" in it. It then also adds the sum of Colomn D to the result.

This took me ages to figure out and it looks far more complex than it should be, but it does do the trick....am i missing something really obvious that can shorten this code?

**Bernie Deitrick** · 08-29-2014, 02:47 PM

No - that covers it. For complex situations, you need complex formulas, if you intend to use just one cell for it.

**daddylonglegs** · 08-29-2014, 03:14 PM

One very minor change you could make - SUBTOTAL allows you to specifiy multiple ranges so no reason you can't include both ranges in one SUBTOTAL function, i.e.

=SUBTOTAL(9,$H$18:$H$190,$D$193:$D$300)+SUMPRODUCT(SUBTOTAL(9,OFFSET($G$18,ROW($G$18:$G$190)-ROW($G$18),0)),($H$18:$H$190="")+0,($J$18:$J$190<>"Red")+0)

Other than that I agree with Bernie - you can get rid of the SUBTOTAL/OFFSET part if you are prepared to have a helper column