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 41=3 and 3*5 = 15 1 ignored 41=3 and 3*5 = 15 31=2 and 2*5 = 10 21=1 and 1*5 = 5 1 ignored 1 ignored 21=1 and 1*5 = 5 31=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:J11)*5)  Rick (MVP  Excel) "nordiyu" <nordiyu(a)discussions.microsoft.com> wrote in message news:616953B5BC0E4B40816A7BED7AA68A2B(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" <JLatham(a)discussions.microsoft.com> wrote in message news:71DB05E5F74B456DA63A27C0514E2182(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 list Next: New to VLOOKUP ...merge addresses? 