Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-16-2011, 11:19 AM
geoffm geoffm is offline using a cell content as a string with COUNTIF Windows 7 32bit using a cell content as a string with COUNTIF Office 2010 32bit
Novice
using a cell content as a string with COUNTIF
 
Join Date: Aug 2011
Posts: 1
geoffm is on a distinguished road
Default using a cell content as a string with COUNTIF

column A contains cells with each a different list of products
ex: A1 = carrots, broccoli, potatoes
A2 = carrots, chives, garlic
A3 = broccoli, garlic, tomatoes

column B contains the list of individual products


B1 = carrots
B2 = broccoli
B3 = tomatoes

I want column C to return the number of occurences in the column A of each product from column B
with the exemple above, C1 should be 2, C2, 2 and C3, 1
the problem is the string in COUNTIF needs wildcards
=COUNTIF(A1:A3;C1) returns 0
however if I use COUNTIF(A1:A3;"*brocoli*") etc it works.
There's a workaround: if I write products in column B with wildcards
B1 = *carrots*
B2 = *broccoli*
B3 = *tomatoes*
it works, but is there a way I can keep the "clean" product names in column B but treat the strings with wildcards?

Last edited by geoffm; 08-16-2011 at 11:58 AM. Reason: solved
Reply With Quote
  #2  
Old 08-16-2011, 11:31 AM
Kimberly Kimberly is offline using a cell content as a string with COUNTIF Windows 7 64bit using a cell content as a string with COUNTIF Office 2010 64bit
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

=countif($a$1:$a$3,"*"&b1&"*")
Reply With Quote
Reply

Tags
cell, countif, string



Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I delete the content of a cell in column if the cell value is more than 1000? Learner7 Excel 2 06-27-2011 05:44 AM
using a cell content as a string with COUNTIF Countif with 2 criteria ibrahimaa Excel 3 05-23-2011 11:23 AM
using a cell content as a string with COUNTIF Cell content splits Mahen Excel 2 05-15-2011 01:53 AM
Package for CD with links to Windows media player content and Flash Shockwave content hectorh PowerPoint 4 10-15-2009 12:22 PM
Extract from String using Wildcard whousedmy Word 0 05-21-2009 01:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:06 PM.


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