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