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?

Find more posts tagged with

Human Resources

Healthcare

Formulas

Reports

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…
How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Find information within a formula
Hello, I am trying to find information within formulas that reference a given cell using CTRL + F. The data is in the cell [2028 (CY)]214. I have tried the following: [2028 (CY)]214, *[2028 (CY)]214, = [2028 (CY)]214, = * [2028 (CY)]214. However, it returns 'no matches found'. Does anyone have advice or is this not…