COUNTIFs formula with Date Range

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

All comments

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

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.

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

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

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

Sure! @Genevieve P.

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

Screen Shot 2022-03-24 at 7.11.38 PM.png

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!

@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")

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 <:

Cheers,

Genevieve