Trying to add numbers that include text.
ahuey
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.
Glenn
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))
Glenn
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))
Mike H
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
ryguy7272
=COUNTIF(B1:B30,"x")
HTH,
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
ryguy7272
=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--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
Dave Peterson
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.
--
Dave Peterson
minyeh
use CSE