AVERAGEIF formula but only for specific subset of data
I am trying to determine average from a referenced table with 2 different filters (i.e. Column A = YES AND Column B - X-Small). I can figure out how to AVERAGEIF to filter based on one column but not 2. Almost like i need AVERAGEIFS like COUNTIFS. Any insight would help. I tried testing AVERAGEIF within an IF Statement but no luck.
Answers
-
Try using an AVG/COLLECT.
=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Not working, unless I have set it up wrong. Let me add more detail on what I am trying to accomplish.
Master Sheet has 3 values:
- Work Effort (Yes/No)
- Shirt size (X-Small, SMall, etc.)
- Duration of Days
I am trying to capture an average of duration days for all entries with Work Effort = No and Shirt Size = X-Small
Then same for other Shirt sizes options.
Thanks in advance.
-
Ok. What exactly do you mean by "not working"? Are you getting an error or an unexpected result?
What exactly is in the [Duration of Days] column? Is it manual entry or a formula? If it is a formula, what exactly is it?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
When i tried AVG(COLLECT...i got a Division by Zero error.
Duration of Days is a calculated fields looking at Start Date and one of 3 Completion dates
-
What is the formula?
Make sure the text you are searching for matches what is in the sheet such as "X-Small" vs "X - Small" (note the spaces).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Actually, I apologize. Original suggestion worked. The #Divide By zero was because that specific cell didnt have any entries to average
-
You can wrap the original formula in an IFERROR to output a zero in those instances.
=IFERROR(original_formula, 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!