Trying to add numbers that include text.

  • Thread starter Thread starter ahuey
  • Start date Start date
A

ahuey

Ex. In Column B there are 30 cells. I need to know the total of the numbers
with xs. I do not need to know the total of cells that have the text xs in it.
Cell 1 has 2 xs (xsmall)
Cell 2 has 3 s (small)
Cell 3 has 2 s
Cell 4 has 3 xs
Cell 5 has 1 xs
I need to know the total of xs is 6. I keep getting 3 or zero. I need to
know what formula to use. Then I can use the same formula to get the total of
s. Please help.
 
ahuey said:
Ex. In Column B there are 30 cells. I need to know the total of the numbers
with xs. I do not need to know the total of cells that have the text xs in it.
Cell 1 has 2 xs (xsmall)
Cell 2 has 3 s (small)
Cell 3 has 2 s
Cell 4 has 3 xs
Cell 5 has 1 xs
I need to know the total of xs is 6. I keep getting 3 or zero. I need to
know what formula to use. Then I can use the same formula to get the total of
s. Please help.
Click to expand...

Assuming your data will always be in the form of a number, followed by a space,
followed by the size, and with your cells 1 through 5 above in column A, put the
following array formula (commit with CTRL+SHIFT+ENTER) in B1:

=SUM(IF(TRIM(RIGHT(SUBSTITUTE(A1:A5," ",REPT(" ",9)),9))="xs",
--TRIM(LEFT(SUBSTITUTE(A1:A5," ",REPT(" ",9)),9)),0))
 
Hi,

This is a classic example of 'I wouldn't start from here if I was you!!'.
With hindsight you are probably now aware you should keep numbers and text in
seperate cell if you want to easily do maths on them.

However, this assumes the space between the numbers and the text in your
sample data actually exists

=SUMPRODUCT((MID(A1:A5,FIND(" ",A1)+1,256)="xs")*(LEFT(A1:A5,FIND(" ",A1)-1)))

Mike
 
Glenn said:
Assuming your data will always be in the form of a number, followed by a
space, followed by the size, and with your cells 1 through 5 above in
column A, put the following array formula (commit with CTRL+SHIFT+ENTER)
in B1:

=SUM(IF(TRIM(RIGHT(SUBSTITUTE(A1:A5," ",REPT(" ",9)),9))="xs",
--TRIM(LEFT(SUBSTITUTE(A1:A5," ",REPT(" ",9)),9)),0))
Click to expand...


Or, with the size in C1 ("xs" for example, but without the quote marks), put
this array formula in B1:

=SUM(IF(ISNUMBER(FIND(" "&C1&" ",A1:A5&" ")),
--TRIM(LEFT(SUBSTITUTE(A1:A5," ",REPT(" ",9)),9)),0))
 
Oh, now I get it.
=SUM(LEN(B1:B30)-LEN(SUBSTITUTE(B1:B30,"xs","")))/2
Note: this is a CSE function; use Ctrl+Shift+Enter, not just Enter.

HTH,
Ryan--
 
I'd insert two columns (C:D) and separate the data into two pieces:

Select column B.
Data|Text to columns
delimited by space
and plop the output into column C (keep the original data if you want)

Then I could use:
=sumif(d:d,"xs",c:c)

And if I needed that combined cell, I'd recombine columns C and D with a
formula:

=c1&" "&d1
and drag down.

And I'd make sure any updates were in the quantity and type fields--not that
original format.
 
Back
Top