Solved

# using countif and applying wildcards to a cell name

Posted on 2011-02-11
389 Views
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
Question by:dwils15
• 4
• 3
• 3
• +1

LVL 9

Expert Comment

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

shouldn't cell AC38 say abc124 instead of "abc 123-1,2" ? Please note the space will affect comparison.
0

LVL 5

Expert Comment

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

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

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

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

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

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

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

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

barry houdini earned 250 total points
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

### Suggested Solutions

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.