SUMIFS only for negative number

Léonie

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

Christian G.

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}; <>"")

Christian G.

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}; <>"")

Léonie

@Christian G. Merci !

L'erreur d'étourdissement... je ne l'avais même pas vue 😅

Bonne journée !