COUNTIFS between dates

Melissa Moseley

I am trying to manage resources for my projects. I have an assigned owner column and an additional 10 columns to capture individual project participants names, per task. I am trying to calculate the number of tasks assigned to each of them, by month. For example: how many tasks are assigned to Paul for Dec 2022.

I can get the total number of tasks by project using =COUNTIFS({Project Plan - SOP Range 4}, $[Primary Column]@row)

When I try adding date ranges, I get unpareseable or incorrect argument.. Here are some of the formulas I've tried:

1. =COUNTIFS({Project Plan - SOP Range 4}, [Primary Column]@row, {Project Plan - SOP Range 5}, <=(11 / 1 / 2022), {Project Plan - SOP Range 7}, >=(11 / 30 / 2022))
2. =COUNTIFS({Project Plan - SOP Range 5},IFERROR((@cell), 0) <= DATE (2022, 11, 30), {Project Plan - SOP Range 7}, IFERROR((@cell), 0) >= DATE (2022, 11, 1),{Project Plan - SOP Range 4}, [Primary Column]@row)
3. =COUNTIFS({Project Plan - SOP Range 7}, @cell >= DATE(2022, 11, 1), {Project Plan - SOP Range 5}, @cell <= DATE(2022, 11, 30), {Project Plan - SOP Range 4}, [Primary Column]@row)

Project Plan - SOP Range 4 are the owner and particpants (EE1-EE10) columns

Project Plan - SOP Range 5 is the Start Date column

Project Plan - SOP Range 7 is the End Date column

*If possible, I'd also like to bring in Status is Not Completed or Cancelled.

I've looked for hours and can't figure out what I am doing wrong

Paul Newcome

You will need to make sure to include the date range and criteria sets in each of the COUNTIFS.

Another option would be to insert a multi-select dropdown column in the source sheet and join all of the names together across the columns on each row.

=JOIN([First Name Column]@row:[Last Name Column]@row, CHAR(10))

Then you could use a single COUNTIFS and reference this column instead of trying to write out multiple COUNTIFS and adding them together.

=COUNTIFS({Start Date}, start date criteria, {End Date}, end date criteria, {New Helper Column}, HAS(@cell, [Primary Column]@row))

Melissa Moseley

@Paul Newcome Hoping you can help with this

Paul Newcome

Are you getting an error for #3?

Melissa Moseley

I get Incorrect Argument Set)

Paul Newcome

Ah. Ok. Sorry. I initially missed the part where your people are spread out across multiple columns.

So... All ranges within a function must be of the same shape and size. If you have one range that is a single column wide, you will get an error if you then try to use a range within that same function that is 10 columns wide.

You will need to write a COUNTIFS for each individual person column and then add all of those together.

=COUNTIFS(..........) + COUNTIFS(..........) + COUNTIFS(..........) + ..........

Other than the ranges not matching in size, that #3 formula is the correct syntax.

Melissa Moseley

Oh wow. Ok, I will try that and will keep you posted.

Melissa Moseley

I updated the formula to this:

=COUNTIFS(

{Project Plan - SOP Range 7}, @cell >= DATE(2022, 11, 1), 

{Project Plan - SOP Range 5}, @cell <= DATE(2022, 11, 30)) 

+ COUNTIFS({Project Schedule- Assigned To}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 2}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 3}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 6}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 8}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 9}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 10}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 11}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 12}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 13}, [Primary Column]@row) 

+ COUNTIFS({Project Plan - SOP Range 14}, [Primary Column]@row)

The new formula is counting all tasks within the date range and then adding the count per participant

i.e. there are 49 tasks that meet the date criteria and there are a total of 18 tasks assigned to participant "A" (for the entire project)....The formula is adding 49+18 = 67.

The formula should look at tasks that meet the date criteria, and only count the tasks assigned to participant "A" for that period.

I'm sure it has to do with my parenthesis and the + sign. What am I missing?

Paul Newcome

You will need to make sure to include the date range and criteria sets in each of the COUNTIFS.

Another option would be to insert a multi-select dropdown column in the source sheet and join all of the names together across the columns on each row.

=JOIN([First Name Column]@row:[Last Name Column]@row, CHAR(10))

Then you could use a single COUNTIFS and reference this column instead of trying to write out multiple COUNTIFS and adding them together.

=COUNTIFS({Start Date}, start date criteria, {End Date}, end date criteria, {New Helper Column}, HAS(@cell, [Primary Column]@row))

Melissa Moseley

You are amazing. That worked. Thank you!

Paul Newcome

Happy to help. 👍️