Fonction Date + Counif

Hello everyone,

I am looking for a formula allowing me to return the projects in progress during the month. In my logic the formula gives this:

IF(AND({2022 Plage 1}@row >=[Colonne5]2; {2022 Plage 1} <= [Colonne5]3); COUNTIF({2022 Plage 2}; [Colonne principale]@row); "")

With :

[Colonne5]2 =DATE(YEAR(TODAY()); MONTH(TODAY()); 1)

[Colonne5]3 =IFERROR(DATE(YEAR(TODAY()); MONTH(TODAY()) + 1; 1); DATE(YEAR(TODAY()) + 1; 1; 1)) - 1

But it doesn't work... So can you help me please ?

Sorry for my english

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @azerty64

    Instead of doing an IF statement with an AND to look across sheets, I would put the date criteria within the COUNTIF and make it a COUNTFS, plural.


    Try this:

    =COUNTIFS({2022 Plage 1}; @cell >=[Colonne5]2; {2022 Plage 1}; @cell <= [Colonne5]3; {2022 Plage 2}; [Colonne principale]@row)


    This assumes {2022 Plage 1} is a Date Column referenced. Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @azerty64

    Instead of doing an IF statement with an AND to look across sheets, I would put the date criteria within the COUNTIF and make it a COUNTFS, plural.


    Try this:

    =COUNTIFS({2022 Plage 1}; @cell >=[Colonne5]2; {2022 Plage 1}; @cell <= [Colonne5]3; {2022 Plage 2}; [Colonne principale]@row)


    This assumes {2022 Plage 1} is a Date Column referenced. Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

  • Hello @Genevieve P.

    Thank you very much it works perfectly.

    Acclamations,

    azerty64

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!