vlookup with sum

[jdgel817]

I am trying to do a scorecard based on number of drinks consumed per week. Drinks consumed are entered daily the Week Total would keep a running point total. As drinks are consumed the total points would decrease. I was using a vlookup with a sum shown below:

=IFERROR(VLOOKUP(SUM(E8:K8),Scoring!B28:C34,2,FALSE),"0")

Here is the problem--with this formula the Week Total always starts with a 7 (i.e.sum of E8:K8 is recognized as zero and returns 7 points). How do I keep the same logic, but start the Week Total showing blank or zero?


Mon through Sun are cells E8:K8
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thur[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Week Total[/TD]
[/TR]
[TR]
[TD]# of drinks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Scoring Worksheet
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]# of Drinks[/TD]
[TD]Points Earned[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]>5[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Try this maybe:

=IF(COUNT(E8:K8)=0,0,IFERROR(VLOOKUP(SUM(E8:K8),Scoring!$B$28:$C$33,2,0),0))