How do I sum numbers in cells that have text?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add up numbers in cells that have letters in them. My problem
is that there are two types of letters, and I want them to be added in an
adjacent cell. Confusing? Yeah.

OK, I want to add up the numbers from a row into two columns: "A" and "S".
The user will input, say "A8", "S3", and "A4" in one row. I need a formula to
recognize the difference and give an output of "12" in the "A" column, and
"3" in the "S" column.

I've tried VALUE and RIGHT, but it won't let me do a range of cells, or
recognize the difference between "A" and "S".

Thanks
 
Hi

If your input is in D2 to F2, try this in A2
=SUMPRODUCT((LEFT(D2:F2,1)="A")*(RIGHT(D2:F2)))
and this in S2
=SUMPRODUCT((LEFT(D2:F2,1)="S")*(RIGHT(D2:F2)))

Hope this helps.
Andy.
 
I am trying to add up numbers in cells that have letters in them. My problem
is that there are two types of letters, and I want them to be added in an
adjacent cell. Confusing? Yeah.

OK, I want to add up the numbers from a row into two columns: "A" and "S".
The user will input, say "A8", "S3", and "A4" in one row. I need a formula to
recognize the difference and give an output of "12" in the "A" column, and
"3" in the "S" column.

I've tried VALUE and RIGHT, but it won't let me do a range of cells, or
recognize the difference between "A" and "S".

Thanks
Click to expand...

With your three values in A1:C3

D1 is
=SUMPRODUCT((LEFT($A1:$C1,1)="A")*(RIGHT($A$1:$C$1,LEN($A1:$C1)-1)))

E1 is
=SUMPRODUCT((LEFT($A1:$C1,1)="S")*(RIGHT($A$2:$C$2,LEN($A1:$C1)-1)))


Change the A:E range as appropriate. These allow for more than one
digit, e.g. A25. If there's only one digit in the cell then you can
leave out the LEN($A1:$C1)-1 bit and just have 1.

HTH


Richard Buttrey
__
 
This one worked on your test data:

=SUMPRODUCT(--(LEFT(A1:A3,1)="A"),--(RIGHT(A1:A3)))
 
Thanks for your help, you three. Unfortunately, none of the formulas are
working! I am doing a cut and paste right into my formula bar, but I am
getting a VALUE error.
Here are some specifics of my entries:

My cell range is D5:Q5, and the cell entry could be [A#], or [S#] (where #
is a digit 1-9). I have two cells at S5 and V5 (A and S values,
respectively) that I want the # values summed up.

I was trying IF and VALUE and RIGHT statements, but I can't get those to
work either. Thanks for helping!
 
The following array entered formulae (entered using cntl-shift-enter) assume
that only the first character is non numeric. If the characters are non
numeric after the first character, you will get a Value error (any in the
range.)

=SUM(IF(LEFT($D5:$Q5, 1) = "A",VALUE(RIGHT($D5:$Q5,LEN($D5:$Q5) -1)), 0))

and

=SUM(IF(LEFT($D5:$Q5, 1) = "S",VALUE(RIGHT($D5:$Q5,LEN($D5:$Q5) -1)), 0))

--
Kevin Vaughn


PageRow said:
Thanks for your help, you three. Unfortunately, none of the formulas are
working! I am doing a cut and paste right into my formula bar, but I am
getting a VALUE error.
Here are some specifics of my entries:

My cell range is D5:Q5, and the cell entry could be [A#], or [S#] (where #
is a digit 1-9). I have two cells at S5 and V5 (A and S values,
respectively) that I want the # values summed up.

I was trying IF and VALUE and RIGHT statements, but I can't get those to
work either. Thanks for helping!

PageRow said:
I am trying to add up numbers in cells that have letters in them. My problem
is that there are two types of letters, and I want them to be added in an
adjacent cell. Confusing? Yeah.

OK, I want to add up the numbers from a row into two columns: "A" and "S".
The user will input, say "A8", "S3", and "A4" in one row. I need a formula to
recognize the difference and give an output of "12" in the "A" column, and
"3" in the "S" column.

I've tried VALUE and RIGHT, but it won't let me do a range of cells, or
recognize the difference between "A" and "S".

Thanks
Click to expand...
Click to expand...
 
Back
Top