Best way of Counting a given number of States

Raste

Hi there,

I am trying to have a graph display the count of some things across multiple sheets (~40), as shown in the screenshot.

The only way I could think of for now was to get the data by cell linking and doing some calculations on it in an extra sheet. As it turns out now, doing all of this "manually" for multiple sheets and multiple states is quite a lot of work.

Is there any other, more "comfortable" way of doing this?

 

Cheers

Raste

Paul Newcome

You could set up a grid that has your sheet names going down the leftmost column, and the different statuses going across the top row and your sheet names as children rows underneath of the Total row similar to the below (column headers in bold).

.

Sheet Name           Blue            Green         Red             Yellow

                                Blue            Green          Red              Yellow

- Total

     Sheet A

     Sheet B

     Sheet C

     Sheet D

     Sheet E

     .

     .

     .

     .

.

You could then use a basic COUNTIFS formula with cross sheet references to count how many of each color are on each sheet. I would suggest entering something like this into the cell Blue3 (the top left blank cell within the table/first row containing a sheet name in the column labeled "Blue").

 

=COUNTIFS({Sheet A Status Column}, @cell = Blue$1)

.

You can then dragfill this down the Blue column to the last row containing an actual sheet name. It will generate the same result, but at least the basic formula is now in those cells.

 

Then you would edit each of the formulas, highlight the entire cross sheet reference, and update it to reference the correct sheet's Status column. This will have to be manually done for each of the different sheets, but it will only have to be done this once.

.

Once you have updated the cross sheet references for each of your sheets, enter this into the Blue2 cell (the cell in the column labeled "Blue" in the Total row)

 

=SUM(CHILDREN())

.

Now you can select all of the cell from the total row all the way down the Blue column to the last sheet row. Dragfilling to the right to complete the rest of the Statuses should give you your totals.

 

From there you can reference the same metrics sheet to display whichever set(s) of data you want in a chart widget on a dashboard whether it be individual charts for each sheet or a single chart that only collects from the Totals row.

.

If you need to remove a sheet, just delete the row. If you need to add a sheet, just enter it into the next blank row and indent it so that it is a child row of the Totals row at the top. This ensures that your Totals row will automatically update as each of your sheets are updated and provides the ability to scale to as many or as few sheets as you need.