SUMIFS only for negative number
Hello !
I have two columns to follow the progress of my team planning.
One is for the reference end date, the one I set at the beginning and to which I must stick. And the second one is the adjusted end date, the one that is reviewed once a week to get closer to reality.
I have a formula that counts the variance between these two dates and the results are positive or negative numbers.
I would like to know how many days late I have per person and how many days early I have per person.
For the days late I have :
=SUMIFS({variance end date}; {assigned to}; HAS(@cell; [Assigned to]@row); {status}; <>"Complete"; {variance end date}; >0; {Team}; <>"")
And it works!
But for the days early I have :
=SUMIFS({variance end date}; {assigned to}; HAS(@cell; [Number of overdue tasks]@row); {status}; <>"Complete"; {variance end date}; <0; {Team}; <>"")
And it doesn't work, is it possible to have a SUMIFS for only negative numbers ?
Thanks from France in advance !
Léonie
Best Answer
-
Salut (du Québec)
Tu peux atteindre le même résultat en additionant tout (positif et négatif) et soustraire la partie positive que tu as déjà caculé pour tes jours de retard.
Mais je pense que ton erreur est ici
=SUMIFS({variance end date}; {assigned to}; HAS(@cell; [Number of overdue tasks]@row); {status}; <>"Complete"; {variance end date}; <0; {Team}; <>"")
Ça devrait être :
=SUMIFS({variance end date}; {assigned to}; HAS(@cell; [Assigned to]@row); {status}; <>"Complete"; {variance end date}; <0; {Team}; <>"")
Answers
-
Salut (du Québec)
Tu peux atteindre le même résultat en additionant tout (positif et négatif) et soustraire la partie positive que tu as déjà caculé pour tes jours de retard.
Mais je pense que ton erreur est ici
=SUMIFS({variance end date}; {assigned to}; HAS(@cell; [Number of overdue tasks]@row); {status}; <>"Complete"; {variance end date}; <0; {Team}; <>"")
Ça devrait être :
=SUMIFS({variance end date}; {assigned to}; HAS(@cell; [Assigned to]@row); {status}; <>"Complete"; {variance end date}; <0; {Team}; <>"")
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!