Summarize data by different metrics.

Jared Ross

I have a sheet that I'm trying to find metrics on based on several different columns.

• CONFERENCE
• TRICODE
• ID_CHECK

There's roughly 30 conferences with roughly 15 tricodes within each, and a dozen or two ID_CHECK checkboxes within each tricode.

What I'm looking to do is get a summary or report that shows percentages for multiple metrics.

• % of conferences complete
• % of tricodes within each conference complete
• % of ID_CHECKS checked within each tricode
• % of ID_CHECKS checked within ALL conferences

How do I go about this? I'd also like an option to turn this data into charts of some sort to make it easier to digest as well. I'm pretty sure this is where dashboards come into play, but I'm completely lost on where to start.

Paul Newcome

Are you able to provide some screenshots of the source data for reference?

Jared Ross

Dale Murphy

I can't tell the definition of those metrics from that screenshot.

In other words, how does one tell that a conference is complete?

(Your answer probably lies in a separate sheet, call it a metrics sheet, that you use to count these things). But, to Paul's earlier question, we would need to see more of your data layout.

dm

Jared Ross

All relevant data is in the screen shot. The "ID_CHECKS" checkboxes tell if something is there or not. They represent ID's which are not in the screen shot, but there is a formula there to figure out if the checkbox should be checked or not.

IE - CONFERENCE EAST has 4 TRICODES. Each of those TRICODES has 10 checkboxes. Those checkboxes are checked to note an ID code that is accounted for.

In the above screenshot, if it was only that one EAST conference, PAR would be 70% complete, and CDR would be 85% (6 of the 7 accounted for). EAST conference would be roughly 76% complete (13 out of 17).

Neil Watson

=COUNTIFS(CONFERENCE1:CONFERENCE17, "EAST", TRI1:TRI17, "CDR", [ID_CHECK]1:[ID_CHECK]17, 1) / COUNTIFS(CONFERENCE1:CONFERENCE17, "EAST", TRI1:TRI17, "CDR")