Countif and multiple occurenaces

J

joannlwestfield

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
 
J

Jason Morin

Try:

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

HTH
Jason
Atlanta, GA
 
F

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
 
J

joannlwestfield

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
 
J

joannlwestfield

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
 
F

Frank Kabel

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

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

Top