SUMIFS returns no total with one line of data
We are using this SUMIFS and COUNTIFS as part of our caluclations for critical path. They have been working fine for the entire team, except one member who only had 1 project meeting the criteria. Is that why all the forumulas are returning a zero for her? Is there a way to tweak this so any number listed is returned? The count really stumps me because it should at least retrun 1.
=SUMIFS({CP by GL}, {Status 1}, "Live", {IM Type}, "New Program", {IM Lead}, Metric@row, {Go Live}, >=[Y Start]#, {Go Live}, <=[Y Finish]#)
=COUNTIFS({CP by GL}, >0, {Status 1}, "Live", {IM Type}, "New Program", {IM Lead}, $Metric@row, {Go Live}, >=[Y Start]#, {Go Live}, <=[Y Finish]#)
Best Answer
-
Happy to help. 👍️
And no worries at all. I can't tell you how many times I have stared at a formula for way too long only to have someone else point out something that should have been obvious to me including misspellings. Haha.
Answers
-
Double check that the data has been entered correctly. There are two reasons that I can think of in this particular instance that would generate a zero.
1) (less likely but still a possible issue) The column containing the numbers to be summed is populated with a text string that just looks like a number.
2) (more likely since it is both functions that are not working) Somewhere something doesn't match in the rest of the data.
-
@Paul Newcome Well it was definitely number 2. 🤦 I am so embarassed that I posted this to community when I had one of the names mispelled! A reaffirming lesson that you must have EVERYTHING match! Thank you!
-
Happy to help. 👍️
And no worries at all. I can't tell you how many times I have stared at a formula for way too long only to have someone else point out something that should have been obvious to me including misspellings. Haha.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!