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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!