Total Count for Specific Date Range from another Sheet

Amanda Durham-Barnes

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?

Mike Wilday

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

Amanda Durham-Barnes

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.

Mike Wilday

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.

 

Amanda Durham-Barnes

Yeah I tried that. I included my formula here.

Mike Wilday

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.  

Paul Newcome

You may also want to double check to ensure the referenced columns are formatted as date type columns.

Amanda Durham-Barnes

Oh goodness, this helped. I hadn't thought about resetting the reference as I had copied the formula from this dicussion. Thank you.

Mike Wilday

Awesome! Glad I was able to get you up and going.

Juan Taveras

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