Countif and multiple occurenaces

[Guest]

I need to count more than one occurances in a cell of shirt sizes. For
example in A1: yl,yl,yl three orders of youth large but when in use countif
in B1:Countif(a1,"*yl*") i only get 1. Is there another way to do this? I
have to use wildcards or i get 0 also in a1: there could be ys,ym,yl and then
i would only want one.
Thanks

 

[Jason Morin]

Try:

=SUMPRODUCT(--(SUBSTITUTE(MID(A1,{1,3,6},3),",","")="yl"))

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
another option (for one cell):
=(LEN(A1)-LEN(SUBSTITUTE(A1,"yl",""))/2

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
news:[email protected]...

 

[Guest]

Thanks it works.

Try:

=SUMPRODUCT(--(SUBSTITUTE(MID(A1,{1,3,6},3),",","")="yl"))

HTH
Jason
Atlanta, GA

 

[Guest]

Jason,
How would you do a whole sum a whole column of the data?

 

[Frank Kabel]

Hi
try the following formula:
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1))

where A1:A10 is your range and B1 stores the sub string to search for

 

[Guest]

How would you count a whole column of data for example:
a1: yl,yl,as
a2: yl
a3: yl,as, yl
b: 5 should be the number of yl shirts I need to order.
Thanks

 

[Frank Kabel]

Hi
see your other follow-up question

 

[Guest]

That does not work I get N/A and when I try each formula seperately to see
what is wrong I do not get the right answer. Any help would be appreciated.
Thanks

 

[Frank Kabel]

Hi
formula works for me without a problem. What have you entered exactly

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag