AVERAGEIF formula but only for specific subset of data

Jonathan Kearns

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.

Paul Newcome

Try using an AVG/COLLECT.

=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria))

Jonathan Kearns

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:

1. Work Effort (Yes/No)
2. Shirt size (X-Small, SMall, etc.)
3. 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.

Paul Newcome

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?

Jonathan Kearns

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

Paul Newcome

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

Jonathan Kearns

Actually, I apologize. Original suggestion worked. The #Divide By zero was because that specific cell didnt have any entries to average

Paul Newcome

You can wrap the original formula in an IFERROR to output a zero in those instances.

=IFERROR(original_formula, 0)