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

Find more posts tagged with

Accepted answers

David Tutwiler

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

All comments

David Tutwiler

The easiest thing would be to just add them:

PMO Sys Admin

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

David Tutwiler

No problem, glad it is working.

PMO Sys Admin

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

What error do you get when you try this formula?

PMO Sys Admin

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.