Total Count for Specific Date Range from another Sheet
I am referencing a seperate SmartSheet that I have Admin rights on. I want to count the number of items listed within each specific date range was work by. I would like to then find out how many of those items have been completed.
I have been trying to work with the following formula but it keeps bring back "0".
=COUNTIF({NEO Report-New Hire-Internal Transfer-Cert}, "03/10/2019")
The refenced column was picked through the edit reference option and is referencing a date column. I tried to just start with a single date. However, the dates I am wanting to count would be over a two week period.
How can I make this work?
Comments
-
Hi Amanda, you should try using COUNTIFS which allows you to select items that meet more than one criterion. You could use the same column but check for items that are greater than the earliest date and items that are less than the latest date.
IF your date ranges were between 3/10 and 3/24 This formula will help.
=COUNTIFS({NEO Report-New Hire-Internal Transfer-Cert}, >=Date(2019, 03,10), {NEO Report-New Hire-Internal Transfer-Cert}, <= Date(2019,03,04))
For more on COUNTIFS see: https://help.smartsheet.com/function/countifs
For more on referencing dates see: https://help.smartsheet.com/function/date
-
Thank you for the reply. However, this still supplied "0".
I found some infromation on another discussion and am trying to use a checkbox column to help me.
Not sure if this will work either yet.
-
Did you adjust the date ranges to the ranges you were using? Can you share your formula? I've attached a screenshot showing this working within a single sheet.
-
Yeah I tried that. I included my formula here.
-
Have you tried adding that same formula into your main sheet to see if it counts i there? You'll have to replace the cross-sheet reference with your column name displayed like this.... =Countifs([Column Name]:[Column Name], >=Date(2019, 3, 10), [Column Name]:[Column Name], <=Date(2019, 3, 23))
If you get a result on the actual sheet. I would recreate the cross-sheet reference on your new sheet and make sure you are only selecting the one column.
-
You may also want to double check to ensure the referenced columns are formatted as date type columns.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Oh goodness, this helped. I hadn't thought about resetting the reference as I had copied the formula from this dicussion. Thank you.
-
Awesome! Glad I was able to get you up and going.
-
AWESOME!! I finally got it. Thanks. However, I have another question now. if I want to do a sum of minutes from the dates that I've chosen, how can I make the formula for that. right now i have this one =COUNTIFS({Equipment Downtime Tracker Range 2}, >=DATE(2020, 2, 2), {Equipment Downtime Tracker Range 2}, <=DATE(2020, 2, 8), {Equipment Downtime Tracker Range 3}, "(M) MAINTENANCE"), but i also need to calculate the total minutes of that specific date.
Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!