Update type and date range formula
Hello - I am looking to add to my formula below I have in place, adding a date range. End result is to get totals by the week date range. =COUNTIF([Update Type]:[Update Type], "Identified New Pursuit")
Best Answer
-
Oh no problem! Yes, you can definitely just create it in the sheet.
You could just do one column, a date column, and type in the "Start Date Range" and "End Date Range" into two cells, and select two dates. I've coloured my cells a bit differently so I know that it's a reference chart. I also added in some $ signs to make sure that when I reference these cells the formula knows it's an Absolute reference:
=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >=$[Date Range]$2, [Date of Update]:[Date of Update], <=$[Date Range]$4)
You can read more about referencing cells in formulas in our Help Center (see here).
That should do it! 😊
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Daniel,
You'll need to change the COUNTIF to be a plural COUNTIFS. Then you can add in the Date criteria. Try this:
=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >TODAY(-7), [Date of Update]:[Date of Update], <=TODAY())
Here are some Help Center articles I used to build this formula: COUNTIFS function / TODAY function / Formula tips and tricks
This will look for the range between 7 days ago and today. Is this what you meant by "totals by the week"? Let me know if I've misunderstood what you're looking to do and I'm happy to help further!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you, Genevieve! I used the above formula but still received an error.
Ideally I would like to be able to enter a date range, example: 02/03/20 - 02/10/20. I also tried utilizing a filter for the "Date of Update" column, but it deletes my first 3 columns, row 1 which adds up the totals. From what I can tell, there is no way to freeze multiple cells in SmartSheet as well?
I appreciate your help!!
-
Hi Daniel,
In this case you would want to have two different dates input somewhere in your sheet so that you can reference them in your formula instead of the TODAY function. I would recommend creating a "Start Date Range" field and an "End Date Range" field in your Sheet Summary so that you can reference these (make sure they are a Date Type of field).
Try this:
=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >= [Start Date Range]#, [Date of Update]:[Date of Update], <=[End Date Range]#)
Then in the future all you have to do is adjust the dates and the formula will automatically re-calculate based on this new information. If you're still getting an error with this formula, please let me know what it is, along with a screen capture of exactly what you put in the cell.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I cant thank you enough for your help! Silly question (perhaps) how do you add Date Range in your sheet?
Dan
-
Hi Daniel,
No problem at all! I'm happy to help.
I used the Sheet Summary field in my example. It can be found by clicking the small sheet icon in the menu on the right:
Then create a New Field and make sure it's a Date type of field:
You could also just use a new Date Column in your sheet, but I prefer Sheet Summary as you only need two cells, not an entire column. You can learn more about Sheet Summaries in our Help Center (click here).
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you again! I do not have access under my current membership. If I were to add a date column and change the headers to "Start Week Date" and "End Week Date" would this work with a formula? If so, can you give me an example.
-
Oh no problem! Yes, you can definitely just create it in the sheet.
You could just do one column, a date column, and type in the "Start Date Range" and "End Date Range" into two cells, and select two dates. I've coloured my cells a bit differently so I know that it's a reference chart. I also added in some $ signs to make sure that when I reference these cells the formula knows it's an Absolute reference:
=COUNTIFS([Update Type]:[Update Type], "Identified New Pursuit", [Date of Update]:[Date of Update], >=$[Date Range]$2, [Date of Update]:[Date of Update], <=$[Date Range]$4)
You can read more about referencing cells in formulas in our Help Center (see here).
That should do it! 😊
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That did it!! Thank you!
-
Wonderful! So glad to hear it.
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
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!