Consolidating switch formula

[andrewb90]

Hello all,

I have a shortened version of my formula, and I wanted to see if anybody had an easier way of doing this. I've got another 30 rows to add to the formula, and I haven't had much luck doing a range.

=(SWITCH(J3,"GONE!",D3,0))+(SWITCH(J4,"GONE!",D4,0))+(SWITCH(J5,"GONE!",D5,0))+(SWITCH(J6,"GONE!",D6,0))+(SWITCH(J7,"GONE!",D7,0))+(SWITCH(J8,"GONE!",D8,0))+(SWITCH(J9,"GONE!",D9,0))

To clarify the formula, Column D has dollar amounts, and J has time formulas which at a certain time, will change to value "GONE!" I need to sum up the values of all those that are "gone"

 

[thisoldman]

Try this.

=SUMPRODUCT((J3:J9="Gone!")*(D3:D9))