Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only numbers?!
Assume values are in A1:E1 and non numeric entries start with a single
letter. =SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1:E 1)-1)*1)) Entered with Ctrl+Shift+enter rather than just enter because this is an array formula. -- Regards, Tom Ogilvy "dove_g" wrote in message ... Row in excel have following values: 9 12 P4 3 P1 In sum I must get this results: SUM (9 + 12 + 4 + 3 + 1 ) = 27 SUM P (1 + 1) = 2 Is that possibe, I have try few formulas without success. Problem is only how to calculate (SUM) all this. Thank you. P.S. I have only letter P, on the left side, so I can use LEFT(x,1)="P". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only numbers?!
On Wed, 3 Sep 2003 07:34:15 -0400, "Tom Ogilvy"
wrote: Assume values are in A1:E1 and non numeric entries start with a single letter. =SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1: E1)-1)*1)) Hm, that's not vorking at all?! No value. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only numbers?!
It works fine - given the assumptions. It was copied from the formula bar -
the cell was displaying the correct sum. If it shows just the sum of the numbers not preceded by letters, then you have not entered it with with Ctrl+Shift+Enter as instructed. However, you say it is displaying nothing - hard for a working function to do that - is the cell displaying the formula. Do you have a space in front of the Equal sign? -- Regards, Tom Ogilvy "dove_g" wrote in message ... On Wed, 3 Sep 2003 07:34:15 -0400, "Tom Ogilvy" wrote: Assume values are in A1:E1 and non numeric entries start with a single letter. =SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1: E1)-1)*1)) Hm, that's not vorking at all?! No value. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum only numbers?!
On Wed, 3 Sep 2003 07:34:15 -0400, "Tom Ogilvy"
wrote: Assume values are in A1:E1 and non numeric entries start with a single letter. =SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1: E1)-1)*1)) Sorry Tom, it's Ok now. I see now that I must have *1 in RIGHT command so with that I get value number. And point is if there is any field empty, empty * 1 gives no value result. So I just add small code: SUM(IF(LEN(A1:E1)=0;0;your formula) Thank you a lot Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions |