How to only Sum values for unique requests with a specific request type

Brett H

Hello,

I have created a sheet to calculate some data points based off of an archive of requests that get pulled from an Automation when Estimated Completion Date and Actual Completion Date are changed, and I am trying to setup a columns to display the Average Days to Completion for each request type to help with estimates for future requests of the same type. Issue I am running into is that my average is off because it is summing duplicate values for the same request that have had comments added to them after being closed and then pulled into the archive.

My current formula is as follows: =IFERROR(SUMIFS({QTR Completion Date Updates - Actual Comp Days}, {QTR Completion Date Updates - Type of Request}, [Request Type]@row) / [Total Requests]@row, 0)

What I am trying to do is add another layer to the SUMIFS criteria to only look at the first instance of a duplicated Request ID, but I keep getting #INCORRECT ARGUMENT. Here is the formula I have tried: =SUMIFS({QTR Completion Date Updates - Actual Comp Days}, {QTR Completion Date Updates - Type of Request}, [Request Type]@row, {QTR Completion Date Updates - Request ID}, DISTINCT({QTR Completion Date Updates - Request ID})

I have also tried using DISTINCT(COLLECT({QTR Completion Date Updates - Request ID})) with no luck.

brianschmidt

Here's an idea. In your sheet with all the requests, create two additional columns. One will be a Auto Number column (in this case referred to as "Row ID"). Then, create a checkbox helper column with the following formula:

=IF(INDEX(COLLECT([Row ID]:[Row ID], [Request ID]:[Request ID], [Request ID]@row), 1) = [Row ID]@row, 1, 0)

This will place a checkmark in all the rows with the first distinct request ID. Then, in your other sheet, you can use that checkbox to calculate your average days to completion.

Hope this helps!:)

brianschmidt

Here's an idea. In your sheet with all the requests, create two additional columns. One will be a Auto Number column (in this case referred to as "Row ID"). Then, create a checkbox helper column with the following formula:

=IF(INDEX(COLLECT([Row ID]:[Row ID], [Request ID]:[Request ID], [Request ID]@row), 1) = [Row ID]@row, 1, 0)

This will place a checkmark in all the rows with the first distinct request ID. Then, in your other sheet, you can use that checkbox to calculate your average days to completion.

Hope this helps!:)

Brett H

That worked like a charm! Had to update a few automations/destination sheets, but that is much easier to do now with this formula.

Thank you!

brianschmidt

Glad to hear it's working!:)