Sumproduct using Tables

J

jonssmaster

I have the following formula that works just fine, except that I need help
with the formula when G33 is blank. I would like the formula cell (H33) to
remain blank if no value has been entered. Any suggestions?

=CHOOSE(SUMPRODUCT(--($A$1:$A$17=E53),--(G53>=$B$1:$B$17),$D$1:$D$17)+SUMPRODUCT(--(E53>=$A$19:$A$23),--(G53>$B$19:$B$23),--(G53<=$C$19:$C$23),$D$19:$D$23)+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24),"Inconsistent","Does
Not Meet","Successful","Excellent","Exceptional","N/A")
 
T

T. Valko

Try this:

=IF(G53="","",CHOOSE(........))

You can save a few keystrokes by replacing that last SUMPRODUCT:

+SUMPRODUCT(--(E53=$A$24),--(G53=$B$24),$D$24)

Can be expressed as:

+(AND(E53=$A$24,G53=$B$24)*$D$24)
 
J

jonssmaster

Thank you for the help. I'm a little new at this, so I'm not sure where to
insert the "If" statement into the existing formula and/or what I should be
entering in the parenthesis after choose.
 
D

David Biddulph

The suggestions that have been given to you are changes to your existing
formula.
Change =CHOOSE(........) to =IF(G53="","",CHOOSE(........))

Similarly in each other case, just change the relevant sections of your
existing formula as Tony suggests.
 
J

jonssmaster

Works Great. Thanks.

One last thing...how would I have N/A show up if G34 is text, like VAC?

=IF(G34="","",CHOOSE(SUMPRODUCT(--($A$1:$A$11=E34),--(G34>=$B$1:$B$11),$D$1:$D$11)+SUMPRODUCT(--(A14:A18=E34),--(G34=B14:B18),D14:D18)+SUMPRODUCT(--(E34>=$A$20:$A$24),--(G34>$B$20:$B$24),--(G34<=$C$20:$C$24),$D$20:$D$24)+(E50=$A$25)*(G50=$B$25)*$D$25,"Inconsistent","Does
Not Meet","Successful","Excellent","Exceptional","N/A")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top