Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-06-2015, 09:11 AM
derohanes derohanes is offline searching a range using a value in another cell Windows Vista searching a range using a value in another cell Office 2007
Advanced Beginner
searching a range using a value in another cell
 
Join Date: Oct 2008
Posts: 67
derohanes is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-06-2015, 10:09 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline searching a range using a value in another cell Windows 7 64bit searching a range using a value in another cell Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,655
Pecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud of
Default

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
Reply With Quote
  #3  
Old 05-06-2015, 11:29 AM
derohanes derohanes is offline searching a range using a value in another cell Windows Vista searching a range using a value in another cell Office 2007
Advanced Beginner
searching a range using a value in another cell
 
Join Date: Oct 2008
Posts: 67
derohanes is on a distinguished road
Default

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
File Type: xlsx Formulae.xlsx (8.9 KB, 13 views)
Reply With Quote
  #4  
Old 05-11-2015, 04:26 AM
beginner beginner is offline searching a range using a value in another cell Windows 7 32bit searching a range using a value in another cell Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Location: Europe
Posts: 45
beginner will become famous soon enough
Default

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)
Reply With Quote
  #5  
Old 05-11-2015, 05:15 AM
derohanes derohanes is offline searching a range using a value in another cell Windows Vista searching a range using a value in another cell Office 2007
Advanced Beginner
searching a range using a value in another cell
 
Join Date: Oct 2008
Posts: 67
derohanes is on a distinguished road
Default

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".
Reply With Quote
  #6  
Old 05-11-2015, 09:59 AM
beginner beginner is offline searching a range using a value in another cell Windows 7 32bit searching a range using a value in another cell Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Location: Europe
Posts: 45
beginner will become famous soon enough
Default

Quote:
Originally Posted by derohanes View Post
I want to look for "EP" instead of "EPA".
Try in B2
Code:
=COUNTIF(Sheet1!$B$2:$C$6;"*"&A2&"*")
Reply With Quote
  #7  
Old 05-11-2015, 12:44 PM
derohanes derohanes is offline searching a range using a value in another cell Windows Vista searching a range using a value in another cell Office 2007
Advanced Beginner
searching a range using a value in another cell
 
Join Date: Oct 2008
Posts: 67
derohanes is on a distinguished road
Default

It doesn't like this command. When I select "OK", it highlights "$C$6". (Please see attached image)
Attached Images
File Type: jpg Capture.JPG (71.0 KB, 14 views)
Reply With Quote
  #8  
Old 05-11-2015, 03:01 PM
beginner beginner is offline searching a range using a value in another cell Windows 7 32bit searching a range using a value in another cell Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Location: Europe
Posts: 45
beginner will become famous soon enough
Default

Quote:
Originally Posted by derohanes View Post
It doesn't like this command. When I select "OK", it highlights "$C$6". (Please see attached image)
Replace the semicolon with a comma
Code:
=COUNTIF(Sheet1!$B$2:$C$6,"*"&A2&"*")
Please you see atachment
Attached Files
File Type: xlsx Formulae.xlsx (11.5 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
searching a range using a value in another cell 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
searching a range using a value in another cell 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
searching a range using a value in another cell Limit cell range use talkinglens Excel 6 12-31-2010 08:43 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft