SOLVED

Wildcard characters not working in COUNTIFS function

Copper Contributor

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).

NameDate
John Doe

30.04.2024

Jane Doe25.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

@VanDerWallas 

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.

Use this instead:

 

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

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!

@VanDerWallas 

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

@VanDerWallas 

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.

Use this instead:

 

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

View solution in original post