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  XSmall). 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))

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 (XSmall, 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 = XSmall
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?

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 "XSmall" vs "X  Small" (note the spaces).

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)
Help Article Resources
Categories
Check out the Formula Handbook template!