COUNTIFS not counting when criteria is a numeric match
I might be missing something basic about formulas, but I have a COUNTIFS formula that counts the number of rows associated with an employees ID and another criteria. It works for all employees except one, who has a numeric ID instead of text. Is this something COUNTIFS just cannot do?
Formula: =COUNTIFS({employee ID on other sheet}, employee ID@row, {other criteria on other sheet}, "criteria")
Example:
Dave: works
Sarah: works
00131851: counts zero
John: works
Best Answer
-
I would find out who posted that entry and find out the name that it should be and just put in the name. Then I would make sure that the ones entering this data know to use the person's actual name. That way you don't have to fix something for one random issue.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
Answers
-
Is your numbers in quotes in your formula? If so, remove the quotes.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
No, no quotes around the number. There are quotes around the second criteria which would always be one of two text options they select on another sheet. This first criteria being their User ID is almost always text, in fact this is the first time I've seen someone with a number for an ID. Anyway no quotes for that criteria.
-
Adding more context:
Formula 1: =COUNTIFS({EFX NPM Quality Evaluations Range 1}, Manager@row, {EFX NPM Quality Evaluations Range 3}, "Scheduled Coaching")
Formula 2: =COUNTIFS({EFX NPM Quality Evaluations Range 1}, Manager@row, {EFX NPM Quality Evaluations Range 3}, "Integrated Coaching")
Other sheet:
-
I would find out who posted that entry and find out the name that it should be and just put in the name. Then I would make sure that the ones entering this data know to use the person's actual name. That way you don't have to fix something for one random issue.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
I think I agree. No SS integration with our systems so it would just be a matter of the one person (far as I know) following a special process. Thanks for the suggestion!
-
No problem! Sometimes the easiest solution is a simple process change and not reworking everything for one issue.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
