Averaging children cells that contain

Maxwony

I know that I am messing up on syntax.

• I'd like to count specific cells that are children of the parent "Q3 2022" that contain the word "key" in the field of "Key/ Swipe/ Code Request"

Mike TV

@Maxwony

This looks completely different than counting children cells that contain the word key in it. Now we're instead calculating the NETDAYS between a request and completed date that contains "key" in a multi-select column?

I'm guessing that "Date Approved" is what you're referring to as the date a key request was made or is there another column not shown in your image?

There aren't any children rows at all then? Can't tell from your screenshot but it looks like there aren't.

If I'm understanding correctly this time, this should work:

Avg Time to Process (Key) formula (which I suggest making a Sheet Summary for it instead of a column since you don't need it repeated down the whole column):

=ROUND(AVERAGEIF([Key/ Swipe/ Code Request]:[Key/ Swipe/ Code Request], CONTAINS("Key", @cell), [Time to Process]:[Time to Process]), 1)

This is Averaging the numbers 20, 0, and 0 which is 6.7 (rounded to 1 decimal). Let me know if the zeros shouldn't be included and we can try to tweak.

If this isn't what you want at all then more information is still needed. Maybe an example of what a correct output would look like that you just manually type out?

Mike TV

@Maxwony

Let me know if there are any corrections or troubleshooting that are needed.

Key Count column formula:

=IF((COUNTIF(CHILDREN([Key/ Swipe/ Code Request]@row), ="key") > 0), COUNTIF(CHILDREN([Key/ Swipe/ Code Request]@row), ="key"), "")

Maxwony

That did not work. I'll try to explain the full process.

Applicants create requests. The type of request is selected from a dropdown multiselect. where their options are key, security code, and/or swipe. I am strictly trying calculating the time (in days) for people that have made any request that includes keys. Some applicants request for all options, some request just keys, some requests swipe.

i've created a 2 columns to calculate the time from when it's request to when its fully processed. I need a formula that only calculates the average "Time to Process" of people that made a key request. Request that includes keys. Ex (Key[x]; Key[x] Swipe[x]; Key[x]; Security Code[x]; Key[x] Swipe[x] Security code[x] )

Mike TV

@Maxwony

This looks completely different than counting children cells that contain the word key in it. Now we're instead calculating the NETDAYS between a request and completed date that contains "key" in a multi-select column?

I'm guessing that "Date Approved" is what you're referring to as the date a key request was made or is there another column not shown in your image?

There aren't any children rows at all then? Can't tell from your screenshot but it looks like there aren't.

If I'm understanding correctly this time, this should work:

Avg Time to Process (Key) formula (which I suggest making a Sheet Summary for it instead of a column since you don't need it repeated down the whole column):

=ROUND(AVERAGEIF([Key/ Swipe/ Code Request]:[Key/ Swipe/ Code Request], CONTAINS("Key", @cell), [Time to Process]:[Time to Process]), 1)

This is Averaging the numbers 20, 0, and 0 which is 6.7 (rounded to 1 decimal). Let me know if the zeros shouldn't be included and we can try to tweak.

If this isn't what you want at all then more information is still needed. Maybe an example of what a correct output would look like that you just manually type out?