I want to use the value in another cell to find the number of times it occurs in a range of cells. This is an example of the search.

=COUNTIF(Sheet1!B1:C5,ISNUMBER(SEARCH("string",SHE ET1!B1:C5)))



I want to look for "string" in range B1:C5 in SHEET1, and count the number of occurances.

This works if I type in "string." However, I want the expression to use the value column A of SHEET2 and put the resulting count in column B SHEET2. So, B1 would have the count of A1 in range SHEET1!B1:C5, and B2 would have the count of A2 in range SHEET1!B1:C5, and so on down the column. So, how do I stick the string that is in A as an expression in the column B?

I hope this is clear.

Could you please post a sample sheet showing expected results?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The attached file has three formulae in SHEET1. SHEET2 has a list of ingredients in column A. I want to look in the formulae in SHEET1 for the value in that is in column A of SHEET2 and place the number of occurances in column B in sheet two. So, the expression in the cell SHEET2!B1 should contain the number of times "EPA" shows up in SHEET1. I can't figure out how to write an expression that will take the string in column A and use it for the search, rather than me typing it in.

Attached Files

Formulae.xlsx (8.9 KB, 15 views)

On Sheet2 try in cell B2 enter the formula and copy it down

Thanks, but I think I oversimplified my example. I want to look for that string, not the necessarily the whole word. Let's assume I want to look for "EP" instead of "EPA".

Try in B2

It doesn't like this command. When I select "OK", it highlights "$C$6". (Please see attached image)

Attached Images

Capture.JPG (71.0 KB, 16 views)

Replace the semicolon with a comma
Please you see atachment

Attached Files

Formulae.xlsx (11.5 KB, 8 views)