Formula's won't work together

Options

Hi! I need to get the below formula to capture both project stages but when I add them together it gives me zero which is incorrect. I need the metric to match the report on the dashboard, any help is appreciated!

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, "Complete")

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, "In Progress")

Tags:

Best Answer

Answers

  • Jeremy C
    Jeremy C ✭✭✭✭✭
    Options

    Krista - A couple of questions for ya.

    Do the formulas work correctly on their own?

    Are these cross referencing other sheets?

    Regards - JC

  • Krista Coffman
    Krista Coffman ✭✭✭✭
    Options

    Hi Jeremy,


    Yes they both work on their own and no they are both pulling from the same sheet.

  • Krista Coffman
    Krista Coffman ✭✭✭✭
    Options

    Hi Nic,

    It tells me that it is unparseable.

    =COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, OR(@{HR Projects - PMO Range 6} = "In Progress", @{HR Projects - PMO Range 8} = "Complete"))

    Because I am pulling all my data from the source sheet it won't let me use the @cell format

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Hmm did you copy/paste my formula over? The one you posted is very different.

    If they only change we are making is at the last criteria to say that you want to count where either of those conditions (In Progress/Complete) exist, then you only need to add one OR at the end as I put above.

  • Krista Coffman
    Krista Coffman ✭✭✭✭
    Options

    That worked, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!