CountIFs and Date range formula

Hi,

need help with a formula. I am trying to count a type from one column 'Finding Rating' against a date range in another column 'Last Completed'. So far I have tried the following but they have all come up with UNPARSEABLE. FYI Conformance is one of the types that I need to count

=COUNTIFS({Finding Rating}, =Conformance, {Last Completed}, >= DATE(2019 7, 1), {Last Completed}, < DATE(2019, 9, 30))

=COUNTIFS(Finding Rating:Finding Rating, "Conformance", Last Completed, >=DATE(2019, 7, 1), Last Completed, <=DATE(2019, 9, 30))

=COUNTIFS({Finding Rating}, Conformance, AND {Last Completed}, (@cell >= DATE(2019, 07, 01), @cell <= DATE(2019, 09, 30)))

Find more posts tagged with

Formulas

IT & Operations

Comments

Paul Newcome

Is the Finding Rating column on the other sheet as well? I notice you entered it as a cross sheet reference in the first formula and as being on the same sheet as the formula in the second one.

If on the other sheet, your first formula should work if you put Conformance in quotes.

=COUNTIFS({Finding Rating}, = "Conformance", {Last Completed}, >= DATE(2019 7, 1), {Last Completed}, < DATE(2019, 9, 30))

VioletB

Hi,

Both the 'Finding Rating' and 'Last Completed' are on the same page. I have been trying to work out the formulas from other user's listing. didn't realise that I added a cross reference to another sheet.

which formula do I need to use when both on the same sheet?

thank you

Andrée Starå

Hi Violet,

Try something like this.

=COUNTIFS([Finding Rating]:[Finding Rating]; "Conformance"; [Last Completed]:[Last Completed]; >=DATE(2019; 7; 1); [Last Completed]:[Last Completed]; <DATE(2019; 9; 30))

The same version but with the below changes for your and others convenience.

=COUNTIFS([Finding Rating]:[Finding Rating], "Conformance", [Last Completed]:[Last Completed], >=DATE(2019, 7, 1), [Last Completed]:[Last Completed], <DATE(2019, 9, 30))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

Andree's response below should work for you.

When referring to a column on the same sheet as the formula, you enter the column name twice with a colon as a separator. You will use square brackets [ ] around the column names if there is a number, space, or special character.

ColumnName:ColumnName

ColumnA:ColumnA

[Column A]:[Column A]

[Column Name]:[Column Name]

[Column1]:[Column1]

[Column!]:[Column!]

When creating a cross sheet reference, the range will only be entered once with the range name surrounded by curly brackets { }.

{Sheet Name Range 1}

{Sheet Name Column Name}

{Any other text you enter to specify a range name}

VioletB

Apologies for taking so long to respond. I was away for work and only returned to office today.

Yes Andree's formula has worked.

thank you

Violet

VioletB

Hi Andree

Apologies for taking so long to respond. I was away for work and only returned to office today.

Formula has worked perferctly

thank you

Violet

Andrée Starå

No worries!

Happy to help!

Paul Newcome

Excellent! Glad we were able to get it working!

Sam Swain

These formulas worked for me too!

I am wondering how to adapt the formula however. I want the formula to pick up a range of 56 days from today's date to 56 days prior. e.g.

=COUNTIFS({Positive}, ="Yes", {Date}, >=DATE(2021, 11, 29), {Date}, <DATE(2022, 1, 16), {Location}, Location@row)

The part of the formula in bold is what I'm missing. Every day new rows are added with todays date. I want it to be registering a continual 56 day period (8weeks) updating every day.

Is this possible?

Sarita Sah

https://community.smartsheet.com/discussion/comment/215401#Comment_215401

Hey, I am using the same formula but its not working. I am counting "Renewal" from 1 column for the year 2022 from a date range column which has 3 years date range. Could you please fix this formula for me?

=COUNTIFS({Project Status - 2022 Range 1}, ="Renewal", {Project Status - 2022 Range 2},>= DATE(2022 01, 01),{Project Status - 2022 Range 2},<DATE(2022 12, 31)))

Genevieve P.

Hi @Sarita Sah

When you say it's "not working", are you getting an error or an incorrect result?

Sarita Sah

https://community.smartsheet.com/discussion/comment/362606#Comment_362606

Getting "Unparseable" error

Genevieve P.

Thanks for clarifying, @Sarita Sah!

It looks like you just have too many closing parentheses at the end. You should only have 2, one for the COUNTIFS() and one for the DATE() function.

Try removing one:

=COUNTIFS({Project Status - 2022 Range 1}, ="Renewal", {Project Status - 2022 Range 2},>= DATE(2022, 01, 01),{Project Status - 2022 Range 2},<= DATE(2022, 12, 31))

As a tip, you can leave off all closing parentheses and Smartsheet will add in the correct number for you:

=COUNTIFS({Project Status - 2022 Range 1}, ="Renewal", {Project Status - 2022 Range 2},>= DATE(2022, 01, 01),{Project Status - 2022 Range 2},<= DATE(2022, 12, 31

Let us know if that worked!

Cheers,

Genevieve

Sarita Sah

https://community.smartsheet.com/discussion/comment/362613#Comment_362613

Hey Genevieve, thanks for taking time to respond and check my formula. It was actually missing a comma next to year. I applied that and it worked. :)

Genevieve P.

Hey @Sarita Sah Aha! Good catch! 🙂

Sign in to join the conversation!

Quick Links

Trending discussions

How to pull one column of data from one sheet to another and be placed in the corresponding box?
Hello, I have one sheet where information is collected for candidates we are looking to hire, and I need a specific column from another sheet of information to feed over to the candidate sheet in the column. The results that are fed over, must align with the correct candidate. Pictures below for reference. I tried…
OTIF
I am trying to calculate OTIF per month. I just added a Helper Column to identify Ontime and Late items. For example, would like to know Aug. 2025 On Time vs Sept. 2025 On Time. How can I achieve this?
Average Collect Unparseable Error
I'm getting an Unparseable error with my average(collect) formula and can't figure out what's wrong with it. =average(Collect({Days to Approval}, {Level}, CONTAINS("Level 1", @cell ), {Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row )) All the references within "{}" brackets are columns…