COUNTIFS between dates & criteria.

Genevieve P.

https://community.smartsheet.com/discussion/comment/329049#Comment_329049

Hi @WendyR

When you say it keeps failing, can you clarify what's happening? Are you receiving an error or an incorrect result?

Testing on your document I receive an output of "6" with your exact formula, which looks to be the correct number. I would make sure that you're selecting the {range} properly in your Cross-Sheet reference, see: Cross-sheet formulas

Cheers,

Genevieve

Paul Newcome

@WendyR Are you getting an error or an incorrect count?

WendyR

Hi, I am getting #INVALID REF

Paul Newcome

@WendyR It sounds like your cross sheet references are not being created properly. Try recreating them from scratch and see if that helps.

Type:

=COUNTIFS(

There should be a blue link in the formula helper box that says something along the lines of "Reference another sheet". Click on that, select the appropriate sheet from the list on the left, select the desired range (click on a column header to reference an entire column), then click on the blue box in the bottom right corner that says something like "Insert Reference".

It should take you back to the sheet you were working in, and you should now see:

=COUNTIFS({Source Sheet Name Range 1}

From here type your comma, then your criteria for that range, comma, then create the next cross sheet reference (or enter the previous cross sheet reference if you are wanting to use it again).

WendyR

I'm using the same source sheet that I'm on and trying to have it look at the "Estimated Metro E Install Date" field. I think I must be using the wrong syntax.

Looking for dates in that field between 8-21-22 and 9-21-22.

=COUNTIFS([Estimated Metro E Install Date], <=DATE(2022, 9, 22), ([Estimated Metro Install Date], >=DATE(2022, 8, 21))

#UNPARSEABLE

Paul Newcome

@WendyR When referencing a column on the same sheet, you repeat it with a colon in between like so:

=COUNTIFS([Estimated Metro E Install Date]:[Estimated Metro E Install Date], <=DATE(2022, 9, 22), [Estimated Metro Install Date]:[Estimated Metro E Install Date], >=DATE(2022, 8, 21))

WendyR

I put in exactly what you have and it still comes up

#UNPARSEABLE

I know there should be 5 that come up

Genevieve P.

https://community.smartsheet.com/discussion/comment/329116#Comment_329116

Hi @WendyR

Can you post a screen capture of the sheet with the formula open?

You'll need to ensure that the column name is spelled exactly correct [in these]:[in these] otherwise you'll get an error.

This Help Article goes through how to reference columns in formulas: Create a Cell or Column Reference in a Formula

Cheers,

Genevieve

WendyR

Yep, you were right. I double checked and I had the incorrect name of the column. Once corrected, it worked!

Thanks!!!

=COUNTIFS([Estimated Metro E Equipment Install Date]:[Estimated Metro E Equipment Install Date], <=DATE(2022, 9, 22), [Estimated Metro E Equipment Install Date]:[Estimated Metro E Equipment Install Date], >=DATE(2022, 8, 21))

Ramzes Z.

Hello, I am looking for some assistance. I am trying to look up dates between 8-21-22 and 9-21-22 on a different sheet. I've attempted to utilize some of the formulas posted on this thread, but I haven't had any luck and keep receiving #unparseable as the outcome. Any assistance would be greatly appreciated. Thanks!

=COUNTIFS([Submission Date]:[Submission Date], <=DATE(2022, 6, 30), [Submission Date]:[Submission Date], >=DATE(2022, 6, 1))

#UNPARSEABLE

Paul Newcome

@Ramzes Z. Double click on the Submission Date column header to ensure you have the spelling exactly correct. If there is an extra space between the words it will be stored on the back-end but not visible.

Submission Date

Submission Date

I put 5 spaces between the two words in the first "Submission Date" but not in the second (screenshot below). You can see how once I posted the comments the extra spaces are not visible. It works the same way in sheets.

If that is not the issue, try swapping the commas out for semi-colons.

Ryon

Hi @Paul Newcome,

Hoping you can help with a variation of the formulas above. I am trying to capture how many instances per row fall within the dates 01 June 2022 and 01 Sep 2022. It feels like it should work but comes back as Unparseable. Can you please take a look and let me know what you think I've done incorrectly?

=COUNTIFS({RBI CAPA Tracker Dept}, [Primary Column]@row, AND(@Cell >=DATE(2022, 06, 01), @cell <=DATE(2022, 09, 01)))

Thank you!

Paul Newcome

@Ryon Your first @cell reference has a capital "C" but it should be a lowercase "c".

Ryon

@Paul Newcome

Thanks, I appreciate the help. I changed that to lowercase and now I get this. Thoughts?

Paul Newcome

@Ryon My apologies. I missed that you also need a second range set up for the date criteria.