Count Task Status across Sheets

For each of our sheets, we use a "Health Status" of Red, Yellow, Green to indicate how that task is going. I would like to create a report which looks across all sheets and simply counts up the total number of tasks which are Red or Yellow. I can easily create the report which shows the list of individual tasks which are Red or Yellow but I cannot figure out how to "sum" this up.

My ultimate goal is to create a Dashboard Chart which will simply show this summed up number in a pie or bar chart but I need to get the report working first before I can make a chart out of it.

Thoughts?

Find more posts tagged with

Formulas

Reports

Comments

Mike Wilday

Rather than count your data in a report, create a new spreadsheet and use an external reference to count the reds across each sheet...

=Countif({external range1}, "Red") + Countif({external Range 2}, "Red")

You just have to add the countifs together. Or if you have a summary cell already... then reference the summary cell either by linking it or creating it only as the range... then just use the =Sum() formula and reference each of those cells from another sheet. See my screenshots for more on that.

summing summary data.gif

Mike Wilday

You can count the RGB across sheets in one of two ways.

Use cross sheet references to count the summary columns using the =Sum({cross refernce range}, {cross reference range2}, etc.)
Or by adding countifs that reference the RGB column together

=Countifs({Cross Reference Column Range1},"Green")+countifs({Cross Reference Range 2}, "Green")

Both will get you the same result you are looking for. See my screenshots for examples.

summing summary data.gif

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?