Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total point
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total point
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total point
=(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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total point
Here is another formula for you to try...
=SUMPRODUCT((A1:J1-1)*5) -- Rick (MVP - Excel) "nordiyu" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total point
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 ... =(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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total point
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. "Rick Rothstein" wrote: 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 ... =(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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What does Excel do when you go over the limit for total data point | Charts and Charting in Excel | |||
Data point on line is not over the point/tick in X axis... | Charts and Charting in Excel | |||
X Y Scatter With Point name on each point ? | Charts and Charting in Excel | |||
Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel |