Prev: MS excell 2007 Data verification list
Next: New to VLOOKUP ...merge addresses?
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" <nordiyu(a)discussions.microsoft.com> 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" <JLatham(a)discussions.microsoft.com> 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 list
Next: New to VLOOKUP ...merge addresses?