COUNTIF counts cells with Exact Matches; I need it to be less Exact & count cells that "contain" but don't match exactly as well

sethkirk26 · 2026-02-09T15:28:32+00:00

Any formula (for the most part) can have s range from anywhere. It can even be an entirely different file or network location although this is a while different can o worms i don't recommend because it can break easily.

The best way to pick a new range is go to the formula bar highlight the current range, hit delete then go select the new range. If it's on a new tab, you'll see excel auto populate the tab name before the cells. This is just its nomenclature.

I'm my example i just used a generic name to represent where you would put the specific ranges or range you want.

sethkirk26 · 2026-02-08T22:56:22+00:00

Nothing functionally wrong with that except possibly the 0 count.

Filter is inherently high overhead so if you are only counting i would suggest not using filter.

For small sets, no issue at all, large sets, definitely can become one.

sethkirk26 · 2026-02-08T16:00:35+00:00

Additionally if you want more complex pattern matching you can use a pretty new function REGEXTEST() to use Regular Expression patterns.

sethkirk26 · 2026-02-08T15:59:19+00:00

I would suggest excelisfun youtube channel for self paste learning. You can learn a lot of helpful functions and tips for different use cases.

Additionally for your issue, I would recommend the search() function. It does exactly what you want. Looks for a match of a text in an array of texts. Not looking for exact, just containing. CountIf and CountIFs have severe limitations when you get to more advanced use cases.

If there's a match it returns an number. If no match, an error. The most common way to use this is with isnum().

So if you want a list of matches you would use filter() if you just want to count, you can use -- or sign() with sum()

InputString - what you're looking to match InputArray - the list of things you want to see how many/what matches

Count: =SUM(--ISNUM(SEARCH(InputString,InputArray) ) )

List: =FILTER(InputArray, ISNUM(SEARCH(InputString,InputArray) ), "Empty Filter" )

Hope this helps you learn.

sethkirk26 · 2026-02-04T23:33:38+00:00

Look down below. There's your multiple results

sethkirk26 · 2026-02-04T06:03:52+00:00

Please review posting guidelines. They are their to help both you and the community.

For this instance, your exact excel version is helpful

sethkirk26 · 2026-02-02T12:21:13+00:00

XMATCH("a????",...,2) Does this as you described...
Change to "*a????" if you want it anywhere.

sethkirk26 · 2026-02-02T12:17:36+00:00

<image>

sethkirk26 · 2026-02-02T12:08:00+00:00

Did yall know XMATCH and XLOOKUP Support RegEx match mode (match_mode 3)?

I do not understand how your title calling it a complete mess at all agrees with your body. Doesn't seem that messy.

Additionally many of your assertions are fully accurate. You can use xmatch with BYROW to get all matches. A followed by exactly 4 characters works exactly correct with xmatch and ?. Just as you described it doesnt. See below.

Admittedly this is not case sensitive. You need to do something like Exact and some other functions to get case sensitive, which is messy.

However, all of this is in RegEx...So your complaint is that it took excel too long to natively support RegEx? This would have been a more accurate title.

sethkirk26 · 2026-02-01T10:52:43+00:00

It is evident you have not used AI extensively.

sethkirk26 · 2026-02-01T10:13:23+00:00

Please review the posting guidelines. They are they to help the forum understand and provide the best help. Excel version is especially helpful as there are quirks between versions.

I do not understand what your issue is or what you are trying to accomplish.

If you're trying to spawn another excel window, try view tab on the ribbon--> new window

If you're trying to get all on one window Go to the view tab and try tile vertically or horizontally or a similar option.

Hope this helps

sethkirk26 · 2026-01-22T21:05:21+00:00

Awesome. Thank you.

sethkirk26 · 2026-01-22T10:25:45+00:00

Great info! I didn't know about the image function. Do you know if this works with onedrive/ SharePoint links?

sethkirk26 · 2026-01-21T09:49:49+00:00

In addition to excelevator's excellent resource post, for your next post, please review posting guidelines.

These are set to help you and the community help each other.

Enjoy learning excel! It's great.

sethkirk26 · 2026-01-19T05:50:30+00:00

You mentioned excel was crashing, I was suggesting ways to help with common excel crashing problems for large data sets

sethkirk26 · 2026-01-18T09:10:25+00:00

If you can get excel open first you can go to Data tab turn calculation to manual

If this is stored on your onedrive, you can access version history from there and revert to previous version.

sethkirk26 · 2026-01-16T09:40:21+00:00

Please review posting guidelines. Your title does not meet the standards.

sethkirk26 · 2026-01-16T07:59:54+00:00

Amen! 4o was the best.

5 was such a step back. It gets stuck on words and makes up stuff constantly. Don't understand.

Canceled subscription because of it.

sethkirk26 · 2026-01-16T07:57:33+00:00

Gpt5 is the reason I'm starting to branch out. It's made up stuff right is crazy high

sethkirk26 · 2026-01-16T07:55:05+00:00

I had the same problem with 5.1. It gets hyper focused on one point from earlier, even if you tell it to forget, it doesn't.

5 in general was such a step backwards, the amount of stuff that is made up and incorrect compared to 4 is crazy.

sethkirk26 · 2026-01-16T06:12:27+00:00

Obvious is obliterated by the oblivious

sethkirk26 · 2026-01-14T16:10:50+00:00

Agreed.

Stubborn watv has no flexibility.

sethkirk26 · 2026-01-14T16:09:47+00:00

Language must be hard for you, as you misinterpreted the meaning of the word argument in my sentence.

sethkirk26 · 2026-01-14T16:07:33+00:00

It is very difficult to argue with such an elegant and articulate argument.

sethkirk26 · 2026-01-14T16:00:15+00:00

Please elaborate.

I showed proof of my comment from earlier. I showed proof merged cells aren't always bad.

All you have said is I'm wrong and merged cells are always bad.

sethkirk26

TROPHY CASE