Solved

using countif and applying wildcards to a cell name

Posted on 2011-02-11
12
406 Views
Last Modified: 2012-05-11
this is what I have typed in cell AP38:
=IF(ISBLANK(AC38),"",COUNTIF($A$36:$U$50,"*"&AC38&"*"))

In cells A36 and U45 it says abc123

in cell AC38 it says abc 123-1,2

I expected to get a return value of 1 in cell AP38. Instead it shows 0.

Troubleshooting, I changed the AP38 formula to:
=IF(ISBLANK(AC38),"",COUNTIF($A$36:$U$50,"*abc123*"))

I got a return value of 2, like i expected.
Why am I not getting the result I expect from using the wildcard? Everywhere I look online it shows to add a wildcard to a cell name in the format of "*"&cell&"*"

Thanks

0
Comment
Question by:dwils15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 9

Expert Comment

by:sah18
ID: 34873892
You mention the value in U45, however, your formula references $U$50 -- was this just a typo in your question, or is this an actual error in your formula?
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34873964
shouldn't cell AC38 say abc124 instead of "abc 123-1,2" ? Please note the space will affect comparison.
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34873993
sorry thats abc123. over here it works fine with the formula COUNTIF($A$36:$U$50,"*"&AC38&"*"), i think the problem is your not actually getting a match.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:dwils15
ID: 34874111
sah. the value is in u45, but should a36:u50, include u45 in the search?

roger. cell ac38 has a space. "abc 123-1,2".
any cell in the range of a36:u50 can have abc123, or abc 123, yet I still dont get a count value returned in cell ap38.
I want the user to be able to put in either abc123, abc 123, abc123-1, abc 123-1, etc. in any cell from a36:u50.
Anytime some of the characters found in ac38 (abc12...) are entered in any cell(s) from a36:u50 I want the count of AP38 to increase.

thanks
0
 
LVL 1

Author Comment

by:dwils15
ID: 34874170
i copied that section to a new worksheet to make it easier to follow:

A1=abc123
B1=abc123
B2=abc 123
B3=abc123-1
B4=abc 123-1
C1==COUNTIF(B1:B4,"*"&A1&"*")
C1 VALUE = 2.

I want C1 value to equal 4 in this scenario.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34874188
Roger is correct, the * wildcard represents any number of other characters (even zero) but for your formula to work the cells in the COUNTIF range must include at least the whole value in AC38, none of them contain "abc 123-1,2" in its entirety.

If you want to allow a space in the input cells then make AC38 just abc123 and then try this formula

=IF(ISBLANK(AC38),"",SUMPRODUCT(--ISNUMBER(SEARCH(AC38,(TRIM($A$36:$U$50)))))

regards, barry

0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34874193
Hello dwils,

your formula checks for * ac38value *, which means that for something to count it has to have the whole string: example "hello abc 123-1,2 there" will count as will "athisbc 123-1,2".

If it does not have the whole string, example "abc 1" or "abc 123", it will not count.

Change the value in ac38 to "abc123" and it should work.


0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34874215
Our posts crossed. For that new setup try this version of my suggestion

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,TRIM(B1:B4))))

regards, barry

[ignore previous version of formula - too many parentheses]

0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34874255
Yep, barry's formula is the way to go. The trim will remove any spaces when counting, so even "a b c 1 2 3" will count.

best of luck,
RK
0
 
LVL 1

Author Comment

by:dwils15
ID: 34878793
In an effort to keep my post simple I only mentioned ac38, but in reality ac38 is just the first cell of a column of over 80 cells that are linked and updated by another spreadsheet. I was just going to copy the formula down the table. So changing the format of what is entered into AC38 isn't a viable option. I need the formula to determine what is in the AC cell and return anything from the A36:U50 range that matches with or without spacing and additional characters.
Sorry I didn't mention this in the beginning, but I was trying to keep it simple.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 34879287
Hello dwils15,

Sorry, in the last formula that I suggested I used TRIM but that won't remove single internal spaces so that won't work. You would need this formula to give the result 4

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,SUBSTITUTE(B1:B4," ",""))))

see attached sheet 1

If you need to have the text in A1 as "abc 123-1,2" then I think you have a dilemna because in that case you aren't just ignoring additional text in B1:B4 but you also want to ignore part of the text in A1. You can do that but you need to have some rules - how much of A1 (or which part of A1) needs to be completely included in the text in B1:B4? In your example, assuming you want to match the first 6 non-space characters of A1 you could adjust my formula like this

=SUMPRODUCT(--ISNUMBER(SEARCH(LEFT(SUBSTITUTE(A1," ",""),6),SUBSTITUTE(B1:B4," ",""))))

see attached sheet 2

I assume that you are using simplified examples of the data. If the above doesn't work for you then it might be better if you can post the actual data, or a closer representation of it

regards, barry
26815749.xlsx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question