SUMIFS returns no total with one line of data

Tina Rustvold

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]#)

Paul Newcome

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.

Paul Newcome

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.

Tina Rustvold

@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!

Paul Newcome

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.