SOLVED

# Wildcard characters not working in COUNTIFS function

Copper Contributor

# Wildcard characters not working in COUNTIFS function

Hi y'all,

this was a real brain scratcher for me, I gave up and went to the internet looking for answers. Here's my situation:
I have two columns. Column 1 has names in it, Column 2 has dates (in this very format, no spaces between the characters).

 Name Date John Doe 30.04.2024 Jane Doe 25.03.2024

Now when I want to get the number of how many times has John Doe appeared in April I was using the formula:

=COUNTIFS(A21:E500;"John Doe";B21:A500;"*.04.2024")

But this formula returns big fat 0.

When I tried to fiddle with it to see if I have my other stuff right, I used the full date without the asterisk:

=COUNTIFS(A21:E500;"John Doe";B21:A500;"30.04.2024")

And this returned "1" as it should have.

My question is - am I using the wildcard characters wrong? I even tried "??.04.2024" string but to no avail. These wildcard characters worked for me successfully in other formulas, I don't know why COUNTIFS has a problem with them.

I am using Microsoft Office Professional Plus 2019 package.

3 Replies
best response confirmed by VanDerWallas (Copper Contributor)
Solution

# Re: Wildcard characters not working in COUNTIFS function

This is because Excel stores dates as numbers not as text.

"30.04.2024" works because it is a complete date, but "*.04.2024" is not recognized as a date.

Also, the ranges are specified incorrectly.

=COUNTIFS(A21:A500; "John Doe"; B21:B500; ">="&DATE(2024; 4; 1); B21:B500; "<="&DATE(2024; 4; 30))

# Re: Wildcard characters not working in COUNTIFS function

Hey, thanks for replying. I effed the ranges by copypasting here, in my doc the ranges are good.
Does it matter that these are dates? In my mind they are strings of characters and I thought that "*.04.2024" means I am looking for a string of characters ending with ".04.2024".

BTW I copy pasted your formula and it works like a charm, thanks again man!

# Re: Wildcard characters not working in COUNTIFS function

Yes, it does matter that the values are dates. They work differently than text strings.

1 best response

Accepted Solutions
best response confirmed by VanDerWallas (Copper Contributor)
Solution

# Re: Wildcard characters not working in COUNTIFS function

This is because Excel stores dates as numbers not as text.

"30.04.2024" works because it is a complete date, but "*.04.2024" is not recognized as a date.

Also, the ranges are specified incorrectly.