total point

[nordiyu]

Sir,
How to count total point for cell A1 to J1:
A B C D E F G I J
1 4 1 4 3 2 1 1 2 3

if, 1=0 point, 1=5 point, 2=10 point, 4=15 point

 

[JLatham]

Are you sure you got the point system correct, you have 2 values for 1 (0 and
5) and no value for 10, so I am going to presume you meant:
1=0, 2=5, 3=10 and 4=15
If that is correct, then this formula will give you the answer (60)
=(SUMIF(A1:I1,">1",A1:I1)-COUNTIF(A1:I1,">1"))*5
That formula simply ignores 1s completely; it gets the total of all values
greater than 1 and effectively subtracts 1 from each of them and multiplies
the result by 5.
so
4-1=3 and 3*5 = 15
1 ignored
4-1=3 and 3*5 = 15
3-1=2 and 2*5 = 10
2-1=1 and 1*5 = 5
1 ignored
1 ignored
2-1=1 and 1*5 = 5
3-1=2 and 2*5 = 10
and finally 15+15+10+5+5+10 = 60

 

[JLatham]

=(SUM(A1:I1)-COUNT(A1:I1))*5
will also give the same result of 60 and it's a simpler formula, so faster
to execute.

 

[Rick Rothstein]

Here is another formula for you to try...

=SUMPRODUCT((A1:J1-1)*5)

 

[Rick Rothstein]

Just pointing out that you wrote the wrong range in your formulas.... you
wrote A1:I1 instead of A1:J1.

 

[JLatham]

Thanks. I missed that. I didn't think of using sumproduct in the way you
did, and had even tried an array first, but that was a total bust.
Looks like it comes down to which ever formula he prefers or feels more
comfortable with.