Count how many times a row is changed to get a percentage

Dakota Haeffner

Hello,

My goal is to return a percentage of how many times we hit our deliver date the first time.

I want to see how many times the deliver date is changed to get a percentage of how many times we hit the first deliver date we put in the field. So to do this I am having the sheet copy the row into a helper sheet and then I have a column counting how many times the same system number shows up in the sheet. I'm struggling a little bit at this point because I'm trying to convert this number now into a percentage. I also am running into a problem because I am counting the child rows only.

Looking for any insight or possible next steps in what I am doing because I'm starting to go cross eyed.🤪

Mike TV

@Dakota Haeffner

So for the percentage, what you're doing to copy it to another sheet and to count the # of times the system number shows up is fine. What do you want the percentage to be of? If it's the percentage of system numbers which had to have a delivery date changed, you'll probably want another helper column that checks a box if the count of changed delivery dates is greater than zero. Then a formula COUNT the # of checkmarks divided by the COUNT of total number of system numbers. You could even omit the helper checkbox column and just COUNTIF the number of columns that have the # of changes being greater than zero divided by the COUNT of the total number of system numbers.

Regarding counting only the child rows, I don't follow. You'll need to include more info or a screenshot.

Dakota Haeffner

Hey @Mike TV Thank you for the help. I'll try to write the example in as much detail as possible and I'll include a screenshot below.

So I have a sheet where we have Product Owners updating ServiceNow numbers they are pulling in from a separate system. We want to get an idea of what percentage of times we hit the Deliver Date the first time and what percentage of times we miss it and the PO updates the Deliver Date because they missed it.

Right now any time the PO changes the Deliver Date Field it copies it into a separate sheet. This sheet has all the helper columns I'm playing with. (See Below)

Deliver Date Count Helper: =COUNTIFS([ServiceNow #]:[ServiceNow #], =[ServiceNow #]@row, [Parent ID]:[Parent ID], ="TOP")

This row shows up more than once: =IF([Deliver Date Count Helper]@row > 1, 1, 0)

This is the first child row: =IF(Suffix@row < 2, 1, 0)

Deliver Date Count Helper 2: =COUNTIFS([This row shows up more than once]@row, 1, [This is the first child row]@row, 1)