calculation

  • Thread starter Thread starter terilad
  • Start date Start date
T

terilad

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
 
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$10,B1&" £",)+0))
 
Hi,
try

=SUMPRODUCT(--(LEFT(A1:A11)="c"), --MID(A1:A11,2,256))

change the letter to fit your needs
 
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
 
Thanks Rick.

Much appreciated.

Mark

Rick Rothstein said:
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
Click to expand...
 
Thanks Jacob,

Got it working.

Mark

Jacob Skaria said:
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$10,B1&" £",)+0))
Click to expand...
 
Back
Top