Countifs - Collect, Index, Match?

Nick Allgauer

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.

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

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. 👍️