Countif and Multiple occurances

J

joannlwestfield

I have a cell that has three enteries in it based on shirt size when i use
countif it only counts one is there a way to make it or use something else to
count all. Here is the data:

A1: yl,yl,yl B1 Countif(a1,"*yl*") - this only returns one. if I do not use
the wild cards I get none. I want b1 to have 3 in it. By the way A1: can
also have ys,yl,ym in it and each one of them should only count as one not
three.
Thanks
 
M

Myrna Larson

If the text you are searching for is in, say, cell C1

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)
 
J

joannlwestfield

That works for one cell but how do you sum all in a range do I have to have a
seperate column and then sum it?
 
F

Frank Kabel

Hi
my formula with Sumproduct should work

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif and multiple occurenaces 9
T-shirt Sizes 2
COUNTIF - help please 10
COUNTIF across worksheets 3
Excel function - countif 2
need help on either doing a loop and counting specific values orusing subtotal function 2
Countif 2
Find >cell_value with COUNTIF. Can't Refer to Cell Value in "quotemarks" 1

Top