#1
|
|||
|
|||
searching a range using a value in another cell
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. |
#2
|
||||
|
||||
Could you please post a sample sheet showing expected results?
__________________
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 |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
On Sheet2 try in cell B2 enter the formula and copy it down
Code:
=COUNTIF(Sheet1!$A$2:$C$6,A2) or =COUNTIF(Sheet1!$B$2:$C$6,A2) |
#5
|
|||
|
|||
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".
|
#6
|
|||
|
|||
Try in B2
Code:
=COUNTIF(Sheet1!$B$2:$C$6;"*"&A2&"*") |
#7
|
|||
|
|||
It doesn't like this command. When I select "OK", it highlights "$C$6". (Please see attached image)
|
#8
|
|||
|
|||
Quote:
Code:
=COUNTIF(Sheet1!$B$2:$C$6,"*"&A2&"*") |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
If id cell range is empty then should not allow to fill any other cell | ubns | Excel Programming | 2 | 04-12-2015 06:31 AM |
If (C1 = refer to a cell in a range) then (show the description in C2) & (corresponding price in C3) | lily | Excel | 9 | 10-23-2014 06:21 AM |
VLOOKUP looking at another cell for its range | Rich18144 | Excel | 1 | 06-24-2014 07:56 AM |
Change formula cell range based on cell value | Scoth | Excel | 4 | 10-25-2012 07:51 AM |
Limit cell range use | talkinglens | Excel | 6 | 12-31-2010 08:43 PM |