COUNTIFs formula with Date Range

Paula Meunier

Hello,

I am trying to create a monthly trend report. The report function is not specific enough for what I need, so in order to do this, I have created a metrics sheet that references 21 different originating sheets. To fill my metrics sheet, I need to use a COUNTIFS formula to reference a date range. I've tried it a few weeks but keep getting an unparseable error. I know for the metrics sheet I will need to write a formula for each individual reference sheet so I am just trying to get the first one down and go from there.

I am unclear on how to handle the references to different sheets. I have tried it both ways below and also tried an AND function:

=COUNTIFS({Archive Avon CMI Weekly Meeting Form Range 2}, <=DATE(2022,2,1), {ARD (Due) Date], >=DATE(2022,2,28), {Archive Avon CMI Weekly Meeting Form Range 3}, “Yes”) (or "No" if 'No')

=COUNTIFS({ARD (Due) Date}, <=DATE(2022,2,1), {ARD (Due) Date], >=DATE(2022,2,28), {Therapy: 150 min+ or 5x/wk?}, “Yes”)

The originating sheet name is "Archive Avon CMI Weekly Meeting Form"

Thanks in advance!

Genevieve P.

Hi @Paula Meunier

It looks like your operators (<= and >=) are the wrong way around. (Apologies! I should have caught this earlier).

You'll want to search for a date greater than the 1st of Feb:

>=DATE(2022, 02, 01)

and less than Feb 28th:

<=DATE(2022, 02, 28)

Try the exact same formula, but adjust > and <:

=COUNTIFS({Archive Avon CMI Weekly Meeting Form Range 1}, >=DATE(2022, 02, 01), {Archive Avon CMI Weekly Meeting Form Range 1}, <=DATE(2022, 02, 28), {Archive Avon CMI Weekly Meeting Form Range 2}, "Yes")

Cheers,

Genevieve

Genevieve P.

Hi @Paula Meunier

It looks like one of your cross sheet references that should be a reference {in these} has a square bracket at the end instead:

{ARD (Due) Date]

If you created a cross sheet reference with the name "ARD (Due) Date" then you should be able to simply correct this by swapping out the closing bracket!

{ARD (Due) Date}

You can check the Sheet Reference Manager in your sheet (right click on any cell) to confirm where each of your {references} are pointing. See: Reference Data from Another Sheet

If this hasn't helped, it would be useful to see a screen capture of your source and destination sheets, identifying what each of your {references} points to, but please block out sensitive data.

Cheers,

Genevieve

Paula Meunier

Hi Genevieve, thanks for your help! I have created the references to both ranges and they show up in the Manage Reference field. Unfortunately fixing the bracket didn't solve it.

Attached is the reference sheet with the ranges I am referencing.

Genevieve P.

Hi @Paula Meunier

Here's an example of how your formula should be structured. You'll need to replace each of the {references} in my example with the name of your references that are looking at the correct columns.

=COUNTIFS({Date Column}, <=DATE(2022, 02, 01), {Date Column}, >=DATE(2022, 02, 28), {Therapy Column}, "Yes")

Note that in your DATE functions you'll want the format to be YYYY, MM, DD, with two values for MM and DD.

You may also want to check your quotation marks to ensure that they're straight up and down. I notice in the example you posted above the quotes are curved:  “Yes” but they should look like this "Yes"

Let me know if this now works for you!

Cheers,

Genevieve

Paula Meunier

@Genevieve P. Thank you! I have applied the appropriate changes and I am no longer receiving an unparseable error, but now I receive an "Invalid Reference" error. When I open "Manage References" it shows both as "Processing" since yesterday. I have deleted and re-added and also logged in and out with no changes.

Genevieve P.

Hi @Paula Meunier

Can you post screen captures of the range being selected in the pop-out window, and the formula open in your sheet?

Paula Meunier

Sure! @Genevieve P.

Paula Meunier

Genevieve P.

Hi @Paula Meunier

Thank you for this!

I can see that the name you have typed in the formula are actually not the same as the names in the Reference Manager. It looks like you may have typed in the column names, instead of using the "sheet reference name".

For example, where you have {ARD (Due) Date} this would need to actually be {Archive Avon CMI Weekly Meeting Form Range...}

The ... would be replaced with whatever number is in that reference name. Let me know if you're still having trouble!

Paula Meunier

@Genevieve P. Thank you!! Soooo close - no more error message now, but the formula result is a 0 for both "yes" and "no" (it should actually be 4 no's and 2 yes's). I'm not sure if this matters but the reference is still showing "processing."

Here's my revised formula:

=COUNTIFS({Archive Avon CMI Weekly Meeting Form Range 1}, <=DATE(2022, 02, 01), {Archive Avon CMI Weekly Meeting Form Range 1}, >=DATE(2022, 02, 28), {Archive Avon CMI Weekly Meeting Form Range 2}, "Yes")

Genevieve P.

Hi @Paula Meunier

It looks like your operators (<= and >=) are the wrong way around. (Apologies! I should have caught this earlier).

You'll want to search for a date greater than the 1st of Feb:

>=DATE(2022, 02, 01)

and less than Feb 28th:

<=DATE(2022, 02, 28)

Try the exact same formula, but adjust > and <:

=COUNTIFS({Archive Avon CMI Weekly Meeting Form Range 1}, >=DATE(2022, 02, 01), {Archive Avon CMI Weekly Meeting Form Range 1}, <=DATE(2022, 02, 28), {Archive Avon CMI Weekly Meeting Form Range 2}, "Yes")

Cheers,

Genevieve

Paula Meunier

@Genevieve P. All good now! Thank you!

I am moving on to the next (similar) column in this metrics sheet which is an AVERAGEIF between a date range. I will make a new topic for that.