| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculation
Hi,
I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculation
If these are text values; try the below formula
Col A Col B Col C C £100.00 C = V £50.00 V = V £50.00 C £100.00 =SUM(IF(LEFT($A$1:$A$10,1)=B1,SUBSTITUTE($A$1:$A$1 0,B1&" £",)+0)) -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi, I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculation
Ia m getting a #value error in the cell.
Any ideas. Mark "Jacob Skaria" wrote: If these are text values; try the below formula Col A Col B Col C C £100.00 C = V £50.00 V = V £50.00 C £100.00 =SUM(IF(LEFT($A$1:$A$10,1)=B1,SUBSTITUTE($A$1:$A$1 0,B1&" £",)+0)) -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi, I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculation
Thanks Jacob,
Got it working. Mark "Jacob Skaria" wrote: If these are text values; try the below formula Col A Col B Col C C £100.00 C = V £50.00 V = V £50.00 C £100.00 =SUM(IF(LEFT($A$1:$A$10,1)=B1,SUBSTITUTE($A$1:$A$1 0,B1&" £",)+0)) -- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi, I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculation
Hi,
try =SUMPRODUCT(--(LEFT(A1:A11)="c"), --MID(A1:A11,2,256)) change the letter to fit your needs "terilad" wrote: Hi, I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculation
Give this array-entered** formula a try...
=SUM(IF(LEFT(A1:A1000)="C",--MID(A1:A1000,4,99))) **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself -- Rick (MVP - Excel) "terilad" wrote in message ... Hi, I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculation
Thanks Rick.
Much appreciated. Mark "Rick Rothstein" wrote: Give this array-entered** formula a try... =SUM(IF(LEFT(A1:A1000)="C",--MID(A1:A1000,4,99))) **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself -- Rick (MVP - Excel) "terilad" wrote in message ... Hi, I have a range of cells that need to be input by data pounds preceded by a letter V, M, C. e.g. C £100.00, or V £50.00. How can I sum these figures on another sheet under the heading C V and M? Many thanks Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| APR Fee Calculation | Excel Worksheet Functions | |||
| Multi threaded calculation (multi CPU) - impact on calculation spe | Excel Discussion (Misc queries) | |||
| How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
| How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
| range.calculation with UDF not working when calculation is set to automatic | Excel Programming | |||
Hybrid Mode
