asked on

using countif and applying wildcards to a cell name

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

sah18

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?

roger_karam

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

roger_karam

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.

dwils15

ASKER

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

dwils15

ASKER

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.

barry houdini

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

roger_karam

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.

barry houdini

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]

roger_karam

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

dwils15

ASKER

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.

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial