Countifs - Collect, Index, Match?

Hi, I need to count data based on a set of criteria. Here's what I have so far:

=COUNTIFS({Project}, $Max$182, {Last Day Worked}, <2 / 1 / 2021, {Role2}, [Primary Column]@row)

Here's my criteria. Count if:

1) project matches what's in Max182

2) last day worked is less than Feb 1st

3) Role matches what's in the primary column

Any suggestions? It's giving me #INVALID OPERATION. I wonder if I need something more complex like collect, index, match.

Find more posts tagged with

Accepted answers

Paul Newcome

To count for Feb 2021, it would look something like this (change bold portions to match the year and month you want).

=COUNTIFS({Project}, $Max$182, {Last Day Worked}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021), {Role2}, [Primary Column]@row)

All comments

Nick Allgauer

Paul Newcome

To use a date within a formula or function, you need to use a DATE function.

=COUNTIFS({Project}, $Max$182, {Last Day Worked}, @cell < DATE(2021, 2, 1), {Role2}, [Primary Column]@row)

Nick Allgauer

That worked; however, it's not quite doing what I want it to do in terms of date. In the referenced sheet, I have a last day worked column that is in date format, but in the summary sheet where the formula is housed I'm counting how many were in each month. Can I change this to month maybe?

Paul Newcome

To count for Feb 2021, it would look something like this (change bold portions to match the year and month you want).

=COUNTIFS({Project}, $Max$182, {Last Day Worked}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021), {Role2}, [Primary Column]@row)

Nick Allgauer

PERFECTION. Thank you!!

Paul Newcome

Happy to 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.