Excel formula to read contents
 

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

Excel formula to read contents
 

One way:

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

In article ,
Fiona wrote:

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

Excel formula to read contents
 

Thank you!

"JE McGimpsey" wrote:

One way:

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

In article ,
Fiona wrote:

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

Excel formula to read contents
 

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" wrote:

One way:

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

In article ,
Fiona wrote:

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

Excel formula to read contents
 

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?

In article ,
Fiona wrote:

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" wrote:

One way:

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

In article ,
Fiona wrote:

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

Excel formula to read contents
 

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" wrote:

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?

In article ,
Fiona wrote:

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" wrote:

One way:

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

In article ,
Fiona wrote:

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

Excel formula to read contents
 

Modify my last example for your ranges/criteria.

One In article ,
Fiona wrote:

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" wrote:

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?

In article ,
Fiona wrote:

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" wrote:

One way:

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

In article ,
Fiona wrote:

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

Excel formula to read contents
 

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


--
Regards,

Peo Sjoblom



"Fiona" wrote in message
...
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" wrote:

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?

In article ,
Fiona wrote:

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" wrote:

One way:

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

In article ,
Fiona wrote:

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