Summary Data - Counting Cells with Dates and Cells with Multiple Check Boxes

Houston Drum

Hello,

I am trying to create summary data that can help track a few things. I've developed the sheet to have smart conditional formatting, so that we know when an item needs to be returned soon and what the priorities are... I want to get the following "counted" in the sheet summary:

Total Items

Outstanding Items (items not checked as Returned)

Hot Outstanding Items (items checked as HOT, but not checked Returned - we have these shown as the bright yellow in our conditional formatting)

Overdue Items (shown as red, Action Req'd By in the past)

Items Due This Week (shown as orange, Action Req'd By within 7 days)

Items Due Next Week (shown as green, Action Req'd By within 14 days)

It should also be noted, I only want to count the Children cells not the parents. Ideally these would be applied to the entire column and not limited as we could have several thousand rows in the life of this sheet. I had started this code: =COUNTIF(CHILDREN(Returned1:Returned1001), 0) + "" ... but I am not sure if this is the right starting point. Apologies if this is obvious, I am just digging into Smartsheet and learning the ropes with formulas, and can't seem to find the right problem in the forum.

Any help would be greatly appreciated!

Mike Wilday

Hi Houston, I totally missed the explanation. I am moving too fast. Here are some formulas you could use to count.

Counts all of the HOT items that are not returned

=countifs( Returned:Returned, 0, Hot:Hot, 1)

Counts all of the Overdue items

=countifs(Returned:Returned, 0, [Action Req'd By:]:[Action Req'd By:], < Today())

Counts items in the next seven days

=countifs(Returned:Returned, 0, [Action Req'd By:]:[Action Req'd By:], AND(@cell <Today(7), @cell >=Today()

Counts items due between 7 days from now and less than or equal to 14 days from now.

=countifs(Returned:Returned, 0, [Action Req'd By:]:[Action Req'd By:], AND(@cell <Today(14), @cell >=Today(7)

Houston Drum

Follow up similar question, trying to count cells "In Progress" or "Not Started" in a different sheet:

Mike Wilday

While you can't count conditional formatting coloring, you can use those same requirements to create an IF statement to apply an RYG symbol to a symbol column that could be counted. Have you considered that approach?

Houston Drum

Thanks for the suggestion!

My hope was to be able to avoid having to add symbols, and additional columns. If I can count via check boxes or date ranges, that would be ideal so as to minimize the number of times data needs to be input. unless, you are suggesting creating symbol columns that auto track based on dates, then the symbols get totaled? Not sure what the simplest way to handle this is.

Understood that the conditional formatting is purely visual, it wasn't my intent to try to count the cells based on the conditional formats.

Mike Wilday

Hi Houston, I totally missed the explanation. I am moving too fast. Here are some formulas you could use to count.

Counts all of the HOT items that are not returned

=countifs( Returned:Returned, 0, Hot:Hot, 1)

Counts all of the Overdue items

=countifs(Returned:Returned, 0, [Action Req'd By:]:[Action Req'd By:], < Today())

Counts items in the next seven days

=countifs(Returned:Returned, 0, [Action Req'd By:]:[Action Req'd By:], AND(@cell <Today(7), @cell >=Today()

Counts items due between 7 days from now and less than or equal to 14 days from now.

=countifs(Returned:Returned, 0, [Action Req'd By:]:[Action Req'd By:], AND(@cell <Today(14), @cell >=Today(7)

Mike Wilday

You can put those formulas in the Sheet summary area of your sheet so the totals don't mess with your current sheet data. Then you can put that data onto a dashboard using a metrics widget.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Houston Drum

This worked perfectly thank you so much!!

Houston Drum

FYI I applied the same logic for the second spreadsheet which worked perfectly:

=COUNTIF(CHILDREN(Status:Status), ="In Progress")

=COUNTIF(CHILDREN(Status:Status), ="Not Started")

Mike Wilday

Awesome! Glad I was able to help you out.