From: nordiyu on 4 Apr 2010 00:38 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 From: JLatham on 4 Apr 2010 01:19 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 "nordiyu" wrote: > 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 From: JLatham on 4 Apr 2010 01:21 =(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. "nordiyu" wrote: > 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 From: Rick Rothstein on 4 Apr 2010 02:12 Here is another formula for you to try... =SUMPRODUCT((A1:J1-1)*5) -- Rick (MVP - Excel) "nordiyu" wrote in message news:616953B5-BC0E-4B40-816A-7BED7AA68A2B(a)microsoft.com...> 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 From: Rick Rothstein on 4 Apr 2010 02:13 Just pointing out that you wrote the wrong range in your formulas.... you wrote A1:I1 instead of A1:J1. -- Rick (MVP - Excel) "JLatham" wrote in message news:71DB05E5-F74B-456D-A63A-27C0514E2182(a)microsoft.com...> =(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. > > "nordiyu" wrote: > >> 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  |  Next  |  Last Pages: 1 2 Prev: MS excell 2007 Data verification listNext: New to VLOOKUP ...merge addresses?