# Need help with AVERAGEIF formula

#### leopardhawk

##### Well-known Member
Hello forum friends, this formula below is working but it is also including data for 2020 and 2021 which are both currently 'blank'.

Code:
``=AVERAGE('2018'!I257,'2019'!I208,'2020'!I257,'2021'!I257)``

I tried the following and I am getting an error ("You've entered too many arguments for this function."). I just want it to ignore 2020 and 2021 until there is data there.
Any ideas?

Code:
``=AVERAGEIF('2018'!I257,'2019'!I208,'2020'!I257,'2021'!I257,"<>0")``

Cheers!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Twollaston

##### Board Regular
Where is your range of cells to average?
And with so many criteria, you have to use =AverageIfs() I believe
Which will need your average range, and then criteria range, criteria, criteria 2 range, criteria2, etc...

Hope that helps

#### Twollaston

##### Board Regular
As far as I can tell from research, you cannot averageif in a non-contiguous range. Meaning they all have to be next to each other. You can average in a non-contiguous range, but not with criteria. From what I've read, it is possible to do if you adjust your data, for instance add a new column at the end of the sheet and put an x in each cell, that way you can grab an entire range for each column, and say sum if or average if there is an "x" in column z

Sorry that's not much help

#### DanteAmor

##### Well-known Member
Try:

=AVERAGE('2018:2021'!I257)

But the cells in 2020 and 2021 must be blank.

#### leopardhawk

##### Well-known Member

DanteAmor, the cells in 2020 and 2021 are blank but it is including them in the AVERAGE, thereby skewing the result. Here are the first two cells, 2018 and 2019. Note also that on the 2019 sheet, the target cell is I208.

Excel 2016 (Windows) 32 bit
I
257
\$ 3.22
 Sheet: 2018

Excel 2016 (Windows) 32 bit
I
208
\$ 3.11
 Sheet: 2019

I should be getting an AVERAGE of \$3.16 but, because 2020 and 2021 are blank (see below), I am getting an AVERAGE of \$1.58.

Excel 2016 (Windows) 32 bit
I
257
\$ -
 Sheet: 2020

Excel 2016 (Windows) 32 bit
I
257
\$ -
 Sheet: 2021

Thanks!

#### leopardhawk

##### Well-known Member
Okay, this works... bit convoluted, but it works...

Code:
``=('2018'!I257+'2019'!I208+'2020'!I257+'2021'!I257) / (('2018'!I257<>0)+('2019'!I208<>0)+('2020'!I257<>0)+('2021'!I257<>0))``

Cheers!

#### DanteAmor

##### Well-known Member
DanteAmor, the cells in 2020 and 2021 are blank but it is including them in the AVERAGE, thereby skewing the result. Here are the first two cells, 2018 and 2019. Note also that on the 2019 sheet, the target cell is I208.

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
\$ 3.22

<tbody>
</tbody>
 Sheet: 2018

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]208[/COLOR]​
\$ 3.11

<tbody>
</tbody>
 Sheet: 2019

<tbody>
</tbody>

I should be getting an AVERAGE of \$3.16 but, because 2020 and 2021 are blank (see below), I am getting an AVERAGE of \$1.58.

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
\$ -

<tbody>
</tbody>
 Sheet: 2020

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]257[/COLOR]​
\$ -

<tbody>
</tbody>
 Sheet: 2021

<tbody>
</tbody>

Thanks!

The cell must be empty. I guess you have a formula, if the result of the formula is 0, then you must change the result of the formula to ""
For example:

=IF(SUM(I252:I255)=0,"",SUM(I252:I255))

Replies
8
Views
269
Replies
2
Views
230
Replies
2
Views
140
Replies
0
Views
145
Replies
0
Views
221

1,129,312
Messages
5,635,491
Members
416,860
Latest member
coen078

### 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