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 wrote:
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.

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 wrote:
ahuey wrote:
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.

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))


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))

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

"ahuey" wrote:

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.

=SUMPRODUCT(--(B1:B30="x"))
=COUNTIF(B1:B30,"x")

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ahuey" wrote:

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.

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--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

=SUMPRODUCT(--(B1:B30="x"))
=COUNTIF(B1:B30,"x")

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ahuey" wrote:

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.

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.



ahuey wrote:

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.

--

Dave Peterson

=SUMPRODUCT((MID(A1:A5,1,FIND(" ",A1:A5)-1))*(RIGHT(A1:A5,2)="xs"))

use CSE