CountIFs and Date range formula

VioletB · 10/07/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)))

Paul Newcome · 10/08/19

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 · 10/09/19

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å · 10/09/19

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 · 10/09/19

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 · 10/14/19

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

VioletB · 10/14/19

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å · 10/15/19

No worries!

Happy to help!

Paul Newcome · 10/15/19

Excellent! Glad we were able to get it working!

Sam Swain · 01/14/22

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 · 02/16/23

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. · 02/16/23

Hi @Sarita Sah

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

Sarita Sah · 02/16/23

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

Getting "Unparseable" error

Genevieve P. · 02/16/23

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 · 02/17/23

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. · 02/17/23

Hey @Sarita Sah Aha! Good catch! 🙂

CountIFs and Date range formula

Comments

Help Article Resources

Categories