Report that groups one criteria and counts another

KGF
KGF ✭✭
edited 06/08/22 in Smartsheet Basics

I have a smartsheet where each row represents a piece of equipment (there are hundreds). Each of these items is serviced by a particular supplier, so each row needs to be reviewed every year for a service contract renewal. When each row is reviewed that row is marked "Complete". When all of the rows have been marked Complete, I can take action on that supplier. What I need is way to quickly see if all the rows serviced by a particular supplier are Complete.

I want to see the status of all the rows for each supplier, so I need them grouped by supplier, and then I need to know how many rows of each supplier have been completed. Ideally, for each supplier I'd see how many rows there are, how many rows have been marked Complete, and (wish list item) the percentage that are done. So a successful report may look like this(I've added dots to aid in formatting for this forum):

Supplier.............................Rows.........Completed............% Complete

Acme Rocket Skates..........100...............64..........................64%

Apex Squirrel Collars...........87................24..........................26%

Bird Feeders 'n' Things........26................26..........................100%

Seeing this I'd know Bird Feeders is ready to go, and Apex needs to be looked at to see why they're taking so long.

I think I need a Report. I've gotten a report to the point where it has grouped the rows first by supplier and then sub-grouped by those that are Complete and those that aren't, but that's not what I'm looking for. This is almost a Pivot Table that I'm looking for, but I haven't figured out how to get a pivot table to do this either.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @KGF

    A good way to do this would be using Parent and Child rows. Then it'll look clean and you can collapse the child rows you're not currently looking at like so:

    Rows formula (goes in parent cell of each parent)

    =COUNT(CHILDREN(Supplier@row))

    Completed Count formula (goes in parent cell of each parent)

    =COUNTIF(CHILDREN(Status@row), ="Completed")

    % Complete formula (goes in parent cell of each parent, format column as a %)

    =SUM([Completed Count]@row / Rows@row)

  • KGF
    KGF ✭✭

    Thank you!! This looks like it will do the trick!