# CountIFs and Date range formula

Options
✭✭
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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}

• ✭✭
Options

Hi

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

No worries!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Excellent! Glad we were able to get it working!

• ✭✭✭✭
Options

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?

• Options

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

Options

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

• Options

Getting "Unparseable" error

edited 02/17/23
Options

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

• Options

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