Formula Help

Options

Looking to count all Stage column = "Active" and add all 'Project End Date' column where date is in the past and in the next 90 days. I have 2 formulas but struggling to merge them:

Formula that works for counting 'in the past' - =COUNTIFS({PMO - Navisite Master Project Register - Stage}, "Active", {PMO - Navisite Master Project Register - End}, <=TODAY(0))

Formula that works for counting 'the next 90 days' - =COUNTIFS({PMO - Navisite Master Project Register -}, >TODAY(), {PMO - Navisite Master Project Register -}, <TODAY(90), {PMO - Navisite Master Project Register - Stage}, "Active")

Therefore tried the following and it doesnt work...I'm sure there is a simpler way to do this - =COUNTIFS({PMO - Navisite Master Project Register - End}, <=TODAY(0), {PMO - Navisite Master Project Register -}, >TODAY(), {PMO - Navisite Master Project Register -}, <TODAY(90), {PMO - Navisite Master Project Register - Stage}, "Active")

Appreciate any help!!!

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    The easiest thing would be to just add them:

    =COUNTIFS({PMO - Navisite Master Project Register - Stage}, "Active", {PMO - Navisite Master Project Register - End}, <=TODAY(0)) + COUNTIFS({PMO - Navisite Master Project Register -}, >TODAY(), {PMO - Navisite Master Project Register -}, <TODAY(90), {PMO - Navisite Master Project Register - Stage}, "Active")

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    The easiest thing would be to just add them:

    =COUNTIFS({PMO - Navisite Master Project Register - Stage}, "Active", {PMO - Navisite Master Project Register - End}, <=TODAY(0)) + COUNTIFS({PMO - Navisite Master Project Register -}, >TODAY(), {PMO - Navisite Master Project Register -}, <TODAY(90), {PMO - Navisite Master Project Register - Stage}, "Active")

  • PMO Sys Admin
    PMO Sys Admin ✭✭
    edited 01/26/23
    Options

    Oh this is wonderful and works a treat!!!! Thank you!!!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    No problem, glad it is working.

  • PMO Sys Admin
    Options

    Apologies...now that works, I'm looking for another formula...using the above but amending it to summing the value of another column, there the above is applicable. I tried this:

    =SUMIFS({Navisite Master Project Register - PS Range 2}, {PMO - Navisite Master Project Register - Stage}, "Active", {PMO - Navisite Master Project Register - End}, <=TODAY(0), {Navisite Master Project Register - PS Range 1}, "Yes") + SUMIFS({Navisite Master Project Register - PS Range 2}, {PMO - Navisite Master Project Register -}, >TODAY(), {PMO - Navisite Master Project Register -}, <TODAY(90), {PMO - Navisite Master Project Register - Stage}, "Active", {Navisite Master Project Register - PS Range 1}, "Yes")

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    What error do you get when you try this formula?

  • PMO Sys Admin
    Options

    I dont get am error but I've added in values to the sheet, refreshed and its not updating, still just stays ay £0.00

    Let me look into this further then and I'll let you know....thanks again and I appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!