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

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.