Half Donut Chart

Vickie Lim

I wanted to make a half donut chart that shows a count of all the status (in progress, completed, etc). I can do it easily for one sheet, how can I do it for multiple sheets. I tried creating a roll-up report of by sheet name, but all I really want is a 'summary GRAND total' for the half donut display. (so it's a total that will have a half donut visual of 6 completed, 16 cancelled, etc). WHat's the best way of doing it? If there a way I can use a report to sum up those numbers? Thank you.

Genevieve P.

Hi Vickie,

Instead of a Report, you'll want to build this in a helper sheet so you can format it how the chart widget will need. How is this status being populated in the different sheets? Is this from one Status column in each sheet?

If so, the way to do this would be to use a cross-sheet COUNTIF formula to count a specific status in one sheet.

=COUNTIF({Status in Growth}, Status@row)

Then you can use the + function to add that to the COUNTIF of a different sheet with the same status, etc.

For example:

=COUNTIF({Status in Growth}, Status@row) + COUNTIF({Status in Marketplace}, Status@row) + COUNTIF({Status in Marketing Operatior}, Status@row)

Etc. This will give you a total number for whatever status is specified in the column Status:

You'll only need to build the formula once, then you can drag-fill down to search for each type of status. This will format the chart in a way that the Donut can read. Does this make sense? Each reference {in these} would be a cross-sheet reference where you'll need to select the new sheet & new column.

Let me know if this works for you!

Cheers,

Genevieve

Vickie Lim

Make sense, @Genevieve P

I tried using this formula to test the initial number before adding more sheets, but it's giving me "INVALID REF"

=COUNTIF({Status in Marketing Operations}, Status@row)

Any clue why?

Curious newbie wants to know, thanks.

Vickie Lim

Ha, user error. Please ignore my question. I have to click the actual range of the column for it to work. Initially, I just click the whole column, and it was producing an error. Then I tried clicking wox 2 to row 30 of that column and it worked. THANK YOU SO MUCH!!!!!!

Genevieve P.

Hi Vickie,

I'm glad you were able to make it work! However you will want to have the entire column selected in your Cross Sheet Reference if you want this to be live data... with the whole column selected, as new rows appear with new content it will automatically roll up into this formula.

This article has more information on creating Cross Sheet References (see here). There's also a video on this other page (see here).