Solved

using countif and applying wildcards to a cell name

Posted on 2011-02-11
12
389 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
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 9

Expert Comment

by:sah18
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:dwils15
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
personal workbook 13 28
Vba to delete entire row 6 26
TT Copy Formula 3 15
Dynamic Excel Countdown Graphic 21 17
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now