Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum #'s in a cell that has text AND numbers?
I don't know if this is possible, but thought it was worth asking. Is there
a function in excel where it will add the numbers in a cell based on the text that's in it? For example, here's what I want to do: A1: S=8.0 A2: (blank) A3: V=8.0 A4: S=4.0 Based on the cells listed above, what formula can I use to get the following? S=12.0 V=8.0 Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum #'s in a cell that has text AND numbers?
Try these:
Both are array formulas. Entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(IF(LEFT(A1:A4)="S",MID(A1:A4,3,10)+0)) =SUM(IF(LEFT(A1:A4)="V",MID(A1:A4,3,10)+0)) Biff "Shirley" wrote in message ... I don't know if this is possible, but thought it was worth asking. Is there a function in excel where it will add the numbers in a cell based on the text that's in it? For example, here's what I want to do: A1: S=8.0 A2: (blank) A3: V=8.0 A4: S=4.0 Based on the cells listed above, what formula can I use to get the following? S=12.0 V=8.0 Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum #'s in a cell that has text AND numbers?
Data Text to Columns select Delimited Next select Other then enter =
in the square box Finish Then =SUM(B1:B4) "Shirley" wrote: I don't know if this is possible, but thought it was worth asking. Is there a function in excel where it will add the numbers in a cell based on the text that's in it? For example, here's what I want to do: A1: S=8.0 A2: (blank) A3: V=8.0 A4: S=4.0 Based on the cells listed above, what formula can I use to get the following? S=12.0 V=8.0 Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum #'s in a cell that has text AND numbers?
Pretty sure there isn't a function, but you could get the result in a
few steps. Enter =IF(SEARCH("S=",A1)=1,MID(A1,3,9),"") into B1, copy the formula down, then highlight the whole of column B, copy, then paste special values, then hit DataText to ColumnsNextFinish. The sum of column B will then be the sum of S. Same thing for V Not sure if you already thought of this, but it's one way to get your answer. Tony On Dec 22, 10:03 am, Shirley wrote: I don't know if this is possible, but thought it was worth asking. Is there a function in excel where it will add the numbers in a cell based on the text that's in it? For example, here's what I want to do: A1: S=8.0 A2: (blank) A3: V=8.0 A4: S=4.0 Based on the cells listed above, what formula can I use to get the following? S=12.0 V=8.0 Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Extract numbers from cell with Text and Numbers | New Users to Excel | |||
Using numbers as numbers in a cell having text | Excel Discussion (Misc queries) | |||
text and numbers same cell and formulas still work (like lotus) | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |