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)))
Comments
-
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))
-
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
-
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.
-
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}
-
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
-
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
-
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.
-
Excellent! Glad we were able to get it working!
-
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?
-
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)))
-
Hi @Sarita Sah
When you say it's "not working", are you getting an error or an incorrect result?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Getting "Unparseable" error
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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. :)
-
Hey @Sarita Sah Aha! Good catch! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!