I am having trouble with a small excel formula problem. I am trying to use `AVERGEIFS`

. The setup of this function is

```
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
```

The catch is that I want the inclusion of a criteria and its range `[criteria_range1,criteria1]`

to be based on whether a data validation list is `Yes`

or `No`

. So for example if the list is currently set to `Yes`

return:

```
AVERAGEIFS(average_range,criteria_range1,criteria1)
```

If no then return

```
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)
```

I would use an `IF`

function to handle this if I had only one data validation list with `Yes`

and `No`

, but I have several, three to be exact. So I want to set up a formula that will include a `[criteria_range,criteria]`

based on whether its corresponding data validation list is `Yes`

or `No`

.

I hope that makes sense. I am using OSX. Thanks!

## EDIT:

In my problem a `name`

is chosen and with it the corresponding attributes from the dataset below. In the `average`

box below I would love to see the average `value`

for rows with the same attributes in John's case its any rows with blue, type1 = 1, AND type2 = 100. This is easy if I just use a regular `AVERAGEIFS`

function.

What I am trying to figure out is how I could ignore one of the attributes and find the average for that group. So if I select ignore `yes`

for `type1`

then `average`

would return the average `value`

for rows with `color = red`

AND `type2 = 100`