How to use COUNTIFS with two criteria? One is a date range.

Ryan Holguin

Hello All,

I am working on gathers metrics for my team and need help figuring out how to use COUNTIFS with the following criteria:

1. Check box is marked as complete
2. The date is between 01/01/20 and 12/31/20

This will be an annual request, so the date will change year over year, but want to determine what my root formula will be.

Can you help?

Thanks!

Ryan H

Paul Newcome

You forgot the comma/zero/closing parenthesis to finish off the IFERROR.

IFERROR(YEAR(@cell), 0) = 2020

Kelly Moore

Hey @Ryan Holguin

Are you gathering data using a summary field on the same sheet, or are you gathering data from another sheet. I ask because the range format is slightly different between the two examples.

Same sheet

=COUNTIFS([Checkbox column]:[Checkbox column], 1, Date:Date, YEAR(@cell)=2020)

Be sure to change the column names to exactly match your actual column names

Cross sheet reference

=COUNTIFS({Source sheet checkbox column}, 1, {Source sheet date column}, YEAR(@cell)=2020)

If cross sheet, you will have to physically build the references using the formula wizard. You cannot just copy paste.

Will either of these work for you?

Kelly

Ryan Holguin

Hey @Kelly Moore ,

It'll be from a cross sheet reference.

For the cross sheet reference formula, what should be used for the "@cell" location?

The formula is coming back as unparseable:

=COUNTIFS({2021 Monthly Ad Completed}, 1, {2021 Monthly Ad Date Range}, YEAR(@cell)=2020))

That seems to be the part I'm stuck on.

Kelly Moore

Hey

I believe you have an extra parenthesis at the end of your formula. As a quick check, look to see if it's blue or black. A blue parenthesis on the end tells you that you have the correct number of parentheses in your formula - although they might not be in the correct position.

Ryan Holguin

@Kelly Moore,

I took a parenthesis away and now getting an Invalid Data Type error.

I wonder if it's an issue with the data I'm trying to pull from?

Kelly Moore

Hey Ryan

Is it possible to share a screenshot (sensitive data removed) of your data? As the formula is written, your {2021 Monthly Ad Completed} would be expecting your checkbox column. The  {2021 Monthly Ad Date Range} would be expecting a Date column, formatted as a Date column. If there are errors or text in that column, we need to account for that.

Ryan Holguin

@Kelly Moore ,

Here is the "completed" column from the referenced sheet:

Here is the date column from the reference sheet:

I renamed the reference in my formula to make it easier to understand.

I hope that's what you're asking for!

Kelly Moore

Whenever I'm testing a formula and getting unexpected results, I try to remove bits of the formula to better understand what portion of the formula may be causing me errors. This is just for testing purposes.

Just as a test, remove the Date range-criteria from the COUNTIFS

=COUNTIFS({2021 Monthly Ad Completed}, 1)

This should give you some number - without errors.

Then, try the other half.

=COUNTIFS({2021 Monthly Ad Date Range}, YEAR(@cell)=2020)

Of course, make sure the ranges are reflecting the range names you actually have in your sheet.

Ryan Holguin

@Kelly Moore,

I think it's the date range that's giving me the issue. Does the YEAR function have anything to do with it? I've never used it before.

Paul Newcome

It is an issue with blank cells in the date range. Try this for the YEAR portion...

IFERROR(YEAR(@cell), 0) = 2020

Ryan Holguin

@Paul Newcome,

So, should the formula look like the following?

=COUNTIFS({Source sheet checkbox column}, 1, {Source sheet date column}, YEAR(@cell)=2020), IFERROR(YEAR(@cell), 0) = 2020)

Paul Newcome

No. You would replace

YEAR(@cell) = 2020

with

IFERROR(YEAR(@cell), 0) = 2020

Ryan Holguin

@Paul Nelson,

Now, I am getting the #Incorrect Argument Set error:

=

Paul Newcome

You forgot the comma/zero/closing parenthesis to finish off the IFERROR.

IFERROR(YEAR(@cell), 0) = 2020

Ryan Holguin

@Paul Newcome,

Got it! Thank you both for your help.

Thanks,

Ryan H

Paul Newcome

Happy to help. 👍️