How to use COUNTIFS with two criteria? One is a date range.
Hello All,
I am working on gathers metrics for my team and need help figuring out how to use COUNTIFS with the following criteria:
 Check box is marked as complete
 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
Best Answer

You forgot the comma/zero/closing parenthesis to finish off the IFERROR.
IFERROR(YEAR(@cell), 0) = 2020
Answers

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

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.

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.

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?

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.

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!

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 rangecriteria 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.

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.

It is an issue with blank cells in the date range. Try this for the YEAR portion...
IFERROR(YEAR(@cell), 0) = 2020

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)

No. You would replace
YEAR(@cell) = 2020
with
IFERROR(YEAR(@cell), 0) = 2020


You forgot the comma/zero/closing parenthesis to finish off the IFERROR.
IFERROR(YEAR(@cell), 0) = 2020


Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!