# Countifs - Collect, Index, Match?

Options
✭✭✭✭
edited 02/22/21

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
edited 02/22/21
Options
• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

PERFECTION. Thank you!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!