COUNTIFS between dates, plus other criteria

Jenna Liu

I am trying to find out how many NEW projects were added on during certain period. I have a date column and a project type column. Here is my formula:

=COUNTIFS({Project List Range 2}, >=DATE(2020, 5, 1), {Project List Range 2}, <=DATE(2020, 5, 31), {Project List Range 3}, "new")

I got Incorrect Argument Set error. I have no problem if I only run the Date or Project Type. What did I do wrong?

Thanks

Jenna

Paul Newcome

Make sure your ranges are the same size. If you are selecting an entire column for one then you need to make sure to select the entire column for the other.

Some additional suggestions (these aren't necessary for your formula to work but could potentially make life a little easier for you):

When using multiple criteria for the same range, I find it is helpful to use an AND statement. That saves a little bit of typing since you only have to enter the range once and helps keep all criteria for that particular range grouped together.

=COUNTIFS({Project List Range 2}, AND(@cell >= DATE(2020, 5, 1), @cell <= DATE(2020, 5, 31)), {Project List Range 3}, "new")

You can also use MONTH and YEAR functions instead of specifying specific beginning and end dates. This (in my opinion) is a little more easy to manage than having to look at a calendar to figure out if there are 30 or 31 days or if that particular year was a leap year and Feb has 29 instead of 28 days. It also cuts down on some typing as well.

=COUNTIFS({Project List Range 2}, AND(MONTH(@cell) = 5, YEAR(@cell) = 2020), {Project List Range 3}, "new")

If you happen to have non-date values (text or blanks) within your range, it could skew your data when using the DATE function or cause an error when using the MONTH/YEAR method, so I like to also incorporate an IFERROR just to be on the safe side.

=COUNTIFS({Project List Range 2}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2020), {Project List Range 3}, "new")

Paul Newcome

Make sure your ranges are the same size. If you are selecting an entire column for one then you need to make sure to select the entire column for the other.

Some additional suggestions (these aren't necessary for your formula to work but could potentially make life a little easier for you):

When using multiple criteria for the same range, I find it is helpful to use an AND statement. That saves a little bit of typing since you only have to enter the range once and helps keep all criteria for that particular range grouped together.

=COUNTIFS({Project List Range 2}, AND(@cell >= DATE(2020, 5, 1), @cell <= DATE(2020, 5, 31)), {Project List Range 3}, "new")

You can also use MONTH and YEAR functions instead of specifying specific beginning and end dates. This (in my opinion) is a little more easy to manage than having to look at a calendar to figure out if there are 30 or 31 days or if that particular year was a leap year and Feb has 29 instead of 28 days. It also cuts down on some typing as well.

=COUNTIFS({Project List Range 2}, AND(MONTH(@cell) = 5, YEAR(@cell) = 2020), {Project List Range 3}, "new")

If you happen to have non-date values (text or blanks) within your range, it could skew your data when using the DATE function or cause an error when using the MONTH/YEAR method, so I like to also incorporate an IFERROR just to be on the safe side.

=COUNTIFS({Project List Range 2}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2020), {Project List Range 3}, "new")