Count Project Categories + Count if Before a date

Hi!

I want to work out a total number of files we have overdue. I have got as far as adding up all projects that do not include the category completed using:

=COUNT([Project Category]:[Project Category]) - COUNTIF([Project Category]:[Project Category], "Complete")

I now want it to not include the project category "With Consultant" too.

Then, i want it to only count the ones remaining if another column called date Completion date is before the start of the month.

Thanks for your help in advance!

Find more posts tagged with

Formulas

Accepted answers

All comments

Lauren Croft

Picture of sheet for reference

Paul Newcome

Try:

=COUNTIFS([Project Category]:[Project Category], AND(@cell <> "Complete", @cell <> "With Consultant"))

Lauren Croft

Thank you that worked! Do you know how I can now add in for it to only count ones where the Completion date is before the current month

Paul Newcome

You would add a new range/criteria set along the lines of

=COUNTIFS(…………….., [Completion Date]:[Completion Date], @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

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.