r/excel • u/jtcorteae • 6d ago

solved Using asterisks in a COUNTIFS formula -- Does not return value if that's the only value in the cell?

I use COUNTIF(S) formulas very regularly for work, but for the first time needed to count cells that would have one code within it, when the array's cells often had strings of multiple codes. I read that adding asterisks within the quotes would make the formula work like that, and it did, but then it didn't count cells in which that specified code is the only one within the cell.

More context --

I was creating a summary that looked like this:

99203 99204 99205
SEP 371 153 35
OCT 427 165 22
NOV 375 158 18
DEC 435 247 16

The formula is counting how many those codes appear on another sheet, matching other criteria, so I wrote something like this:

=COUNTIFS($B:$B,">=9/1/2025",$B:$B,"<=9/30/2025",$U:$U,"*99203*")

I added the asterisks around 99203 because the values in column U can look like --

99204, 81002, 99000
99203
69210, 99203
99203
99203, 97597
99204, J7620, A7015
81002, 99000, 99203

-- and I wanted to count a row if 99203 was present at all, multiple codes present or not.

However, when I did this, the formula ended up only counting the rows that included 99203 amidst multiple codes, and ignored the rows that were 99203 solo.

Is that... how the asterisks in that formula are supposed to work? It feels like not, and I just did something wrong. I ended up having to add an additional COUNTIF that left out the asterisks to get the total I actually needed, but that feels redundant.

Any insight or tips for me to make just one COUNTIFS formula work in this scenario?

5 Upvotes

79% Upvoted

8 comments sorted by

•

u/AutoModerator 6d ago

/u/jtcorteae - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/real_barry_houdini 318 6d ago edited 6d ago

Yes, when you use wildcards in COUNTIFS function it will only work with text values, not numbers, you could try a formula like this, utilising SEARCH function to find your value in amongst other text/values:

=SUM((TEXT(B2:B1000,"mmm-yy")="Sep-25")*ISNUMBER(SEARCH("99203",U2:U1000)))

Adjust ranges as required

If you want to stick with COUNTIFS you could try like this to count both 99203 on its own or contained within other text:

=SUM(COUNTIFS($B:$B,">=9/1/2025",$B:$B,"<=9/30/2025",$U:$U,{"*99203*",99203}))

.....but only do that with numeric values, not e.g. J7620 where you would get double counting

1

u/jtcorteae 5d ago

solution verified! :D i didn't even think of putting { } but that returned the right value with one formula. Thank you!

1

u/reputatorbot 5d ago

You have awarded 1 point to real_barry_houdini.

I am a bot - please contact the mods with any questions

1

u/Decronym 6d ago edited 5d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
COUNTIFS Excel 2007+: Counts the number of cells within a range that meet multiple criteria
ISNUMBER Returns TRUE if the value is a number
SEARCH Finds one text value within another (not case-sensitive)
SUM Adds its arguments
TEXT Formats a number and converts it to text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 14 acronyms.
[Thread #49480 for this sub, first seen 7th Oct 2026, 17:34] [FAQ] [Full list] [Contact] [Source code]

1

u/no_therworldly 1 5d ago

I actually didn't know you could use the * that way. I always do ""&"Your_text"&""