Identifying calculations based on a unique property code.

Guest Experience Shared Services

I am working on a project that evaluates incoming photos for a brand.com website. We approve, reject or correct photos. We track each submission by property code on a daily tracking sheet. At the end of the business day, those entries are automatically moved to an archive sheet.

Variables:

1. There could be more than one submission by each unique property code per day. On the daily archive sheet, I track by submission.
2. I want to report a cumulative summary of the total % approved, % rejected, % corrected by property code.
3. I would like to track this on a helper sheet then use an index match to bring the cumulative % approved, % rejected and % corrected into the daily tracking log.

Guest Experience Shared Services

I wanted to add a bit more info. The table on the left is what the user fills out in Smartsheet daily. It is transferred to an archive sheet daily.

The table on the right is what I would like for results from the archive sheet.

I have the property codes in a database and I'd like to put summary sheet data there.

Based on Smartsheet data limitations, I have about regional archive sheets (e.g. US, Mexico, Canada, Latin America, Caribbean, Europe, Asian, Middle East and South Pacific).

Paul Newcome

You would manually populate the first column in the summary sheet. Then you would use SUMIFS formulas for the Approved, Rejected, and Corrected columns. The Total column would be a SUM function of those three columns. Each of the percentage columns would be the count divided by the Total.

=SUMIFS({Approved Column}, {Code Column}, @cell = [Code Column]@row)

=SUMIFS({Rejected Column}, {Code Column}, @cell = [Code Column]@row)

=SUMIFS({Corrected Column}, {Code Column}, @cell = [Code Column]@row)

=SUM(Approved@row, Rejected@row, Corrected@row)

=Approved@row / Total@row

=Rejected@row / Total@row

=Corrected@row / Total@row