Formula for Counting Tasks by Status by Resource by Coming Due/Due Today across Multiple Sheets

I'm sure this question has been tackled in different ways through other responses, but I'm struggling to find a formula that works without coming back as unparseable. Here is what I'm trying to do:

I have 5 project sheets (will grow into more for the program)
Resources are shared across project sheets (let's say 9 resources in total)

I want to be able to show that Joe Smith has 20 "In Progress" tasks (Status column) across all 5 project sheets that are due today, due in the next 7 days or completed in the past 7 days.

I'm not being successful in writing a formula that is creating the =COUNTIFS with collection of the resource AND Status AND Date to return the count.

This was one attempt at doing it across two sheets only for the 7-day range:

=COUNTIFS({2-IGA Project Plan WBS Primary Resource}, [Primary Column]@row AND({2-IGA Project Plan WBS Status}, [Column2]31 AND {2-IGA Project Plan WBS Range 1}, =TODAY(-7)))

Find more posts tagged with

Accepted answers

All comments

Lucas Rayala

Hi @GAmos, you can't cross-reference two sheets in a single countifs formula, but you can add two countifs together in a single cell/column formula. That would generically look like this:

= COUNTIFS({Criteria Range 1}, CriteriaOne@row) + COUNTIFS({Criteria Range 2}, CriteriaTwo@row)

Also, you aren't using "AND" correctly--as a criteria, it must stand alone. You use an AND statement in a COUNTIFS if you have two criteria a range MUST match. So, if a range MUST be both bigger than 4 AND less than 100, the and would be:

COUNTIFS({target column}, AND(@cell>4, @cell<100))

If you want to select cells in a range if EITHER criteria are met, then you want an OR statement. For instances, if you want to select either cells that contain "Red" or cells that contain "Blue", that would like this:

COUNTIFS({target column}, OR(@cell="Red", @cell="Blue"))

GAmos

Thank you, Lucas. So essentially, I should create and combine multiple COUNTIFS using the '+" by project sheet, where the COUNTIFS statement is using the AND function to pull in Resource AND Status AND Start/End Date ranges.

Lucas Rayala

@GAmos, not quite -- to get at multiple columns on a reference page using a COUNTIFS, you want to separate them into different statements. If all the columns are on the same reference page, you would do this:

=COUNTIFS({resource column}, <resource criteria to filter on>, {Status column}, <Status criteria to filter on>, {Start column}, <Start criteria to filter on>,{End column}, <End criteria to filter on>)

Each of these statements will filter your count by the named criteria. This can be a set value (i.e. "this word") or a refence to a cell on the same page as the formula (i.e. [those words]@row).

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.