Excel formula to read contents

[Guest]

Hi
Does anyone know ho to write an excel formula that will count cells if they
contain part of the specified information.

ie, I want to count if they include '1234' which is in box A1, but the
fields I'm searching in may have 'AB1234', but I still want it to be able to
count them if they include the details in A1.

I hope that makes sense!!
Thank you

 

[JE McGimpsey]

One way:

=COUNTIF(B:B,"*" & A1 & "*")

 

[Guest]

Thank you!

One way:

=COUNTIF(B:B,"*" & A1 & "*")

 

[Guest]

Thank you for you reply, it worked. Is there anyway I can get it to read 2
different boxes at the same time, I'm not sure how to link it?
Thank you

 

[JE McGimpsey]

And what would you do if it read 2 different boxes (cells?).

Are you looking for the count of each? I.e, if A1=1234, A2=abc, does
B1=abc1234 count as 2?

=COUNTIF(B:B,"*" & A1 & "*") + =COUNTIF(B:B,"*" & A2 & "*")

or does it count as 1, but only if it contains both in a certain
arrangement (e.g., "abc1234"?

=COUNTIF(B:B,"*" & A1 & A2 & "*")

or does it count as 1 if it contains both, but in any arrangement (e.g,
"def123456abc")?

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,B1:B100)),
--ISNUMBER(SEARCH(A2,B1:B100)))

or something else?

 

[Guest]

Hi

I want it to count if A1:A100 = B2, and if C1:C100 = D2, I want it to only
count if it contains B2 and D2, or part of it, together.
Thank you

 

[JE McGimpsey]

Modify my last example for your ranges/criteria.

One In article <[email protected]>,

 

[Peo Sjoblom]

Use JE's last formula and change the cell references, that would be
something like


=SUMPRODUCT(--ISNUMBER(SEARCH(B2,A1:A100)),--ISNUMBER(SEARCH(D2,C1:C100)))