# Averageifs

#### johns99

##### Board Regular
Having issues with the below formula

=AVERAGEIFS(\$CY\$6:\$CY\$7808,\$W\$6:\$W\$7808,"x",\$W\$6:\$W\$7808,"+")

I'm trying to get an average for anything in column W with a "x" or "+"

any thoughts on this?

thanks,

John

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### gsistek

##### Well-known Member
All argumements in any ...IFS function need to be met (think AND). In order to use OR logic, multiple functions are often needed. AVERAGEIFS is a bit trickier since you can't simply add multiple ...IFS functions together and call it a day (like you can when using SUMIFS or COUNTIFS). Try the following:
=(SUMIFS(\$CY\$6:\$CY\$7808,\$W\$6:\$W\$7808,"x")+SUMIFS(\$CY\$6:\$CY\$7808,\$W\$6:\$W\$7808,"+"))/(COUNTIFS(\$W\$6:\$W\$7808,"x")+COUNTIFS(\$W\$6:\$W\$7808,"+"))

#### XOR LX

##### Well-known Member
Hi.

You could either use an array formula**:

=AVERAGE(IF(\$W\$6:\$W\$7808={"x","+"},\$CY\$6:\$CY\$7808))

Or a longer, though non-array formula:

=SUM(SUMIF(\$W\$6:\$W\$7808,{"x","+"},\$CY\$6:\$CY\$7808))/SUM(COUNTIF(\$W\$6:\$W\$7808,{"x","+"}))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

#### johns99

##### Board Regular
First I want to thank you both for your responses as they are very helpful...

When I use the following formula

{=AVERAGE(IF(\$W\$6:\$W\$7808={"x","+"},\$CY\$6:\$CY\$7808))}

this returns the answer I was looking for, which is 7.26%...I tested by manually pulling the numbers out of the column and averaging

When I use:
=(SUMIFS(\$CY\$6:\$CY\$7808,\$W\$6:\$W\$7808,"x")+SUMIFS(\$CY\$6:\$CY\$7808,\$W\$6:\$W\$7808,"+"))/(COUNTIFS(\$W\$6:\$W\$7808,"x")+COUNTIFS(\$W\$6:\$W\$7808,"+"))
and
=SUM(SUMIF(\$W\$6:\$W\$7808,{"x","+"},\$CY\$6:\$CY\$7808))/SUM(COUNTIF(\$W\$6:\$W\$7808,{"x","+"}))

I'm getting the same answer, however it is different from the very first answer of 7.26%...it's actually 3.88%

do either of you know why?

#### XOR LX

##### Well-known Member
I cannot think of any reason why those formulas should give different results.

Regards

#### johns99

##### Board Regular
I think it may have something to do with some of the cells being blank

#### johns99

##### Board Regular
So while this formula

{=AVERAGE(IF(\$W\$6:\$W\$7808={"x","+"},\$CY\$6:\$CY\$7808))}

Is taking a total average of any cell with a "x" or "+" in it, the other two are actually dividing the sum and the count...which is why they are coming out identical.

#### johns99

##### Board Regular
Lastly, I've tried to use the similar formula on different criteria....

{=AVERAGE(IF(\$CQ\$6:\$CQ\$7808={"P6","M5"},\$CY\$6:\$CY\$7808))}

But this seems to be coming out #N/A....any idea why this would occur?

Again, your help is much appreciated. thanks.

#### gsistek

##### Well-known Member
The only thought I have is that you have 'x's and/or '+'s in column W that have blanks in the corresponding row in column CY. The approach that had the correct answer skips the blanks, whereas the other two formulas count the number of 'x's and '+'s essentially treating the blanks as zeros. If I were to guess, I'd say half of the 'x's and '+'s have blank cells assosiated with them, since the answers were different by a factor of 2.

#### barry houdini

##### MrExcel MVP
Are the blanks completely blank cells or a blank returned by a formula?

If it's the latter then, yes, that can affect the result - you could also get different results if any of the numbers in \$CY\$6:\$CY\$7808 are text-formatted

Replies
13
Views
370
Replies
4
Views
207
Replies
0
Views
137
Replies
4
Views
282
Replies
19
Views
507

1,191,707
Messages
5,988,230
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back