Forum Discussion
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.
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))
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!Yes, it does matter that the values are dates. They work differently than text strings.
