Averaging children cells that contain
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"
Best Answer
-
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?
Answers
-
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"), "")
-
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] )
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!