# 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!!!

• Overachievers Alumni
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")

• Overachievers Alumni
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")

• edited 01/26/23
Options

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

• Overachievers Alumni
Options

No problem, glad it is working.

• 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")

• Overachievers Alumni
Options

What error do you get when you try this formula?

• 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!