Formula Help
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
-
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
-
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")
-
Oh this is wonderful and works a treat!!!! Thank you!!!
-
No problem, glad it is working.
-
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")
-
What error do you get when you try this formula?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!