# AVERAGEIF formula but only for specific subset of data

Options

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.

Tags:

• ✭✭✭✭✭✭
Options

Try using an AVG/COLLECT.

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

• Options

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.

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

=IFERROR(original_formula, 0)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!