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?
Comments
-
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.Β
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!