# I´m trying to do average IFS in a table, averaging 2 columns but can´t do it. Pls help.

#### delfinpedro

##### New Member
Good day, Im a little experienced with tables and have no issues with single sum or average ranges. Even dynamic, etc. Now I´m trying to average 2 columns based on criteria:
=AVERAGEIFS(AMR[[#All],[2001Jan]:[2002Feb]],AMR[[#All],[Act]],[@Act]) It´s returning #VALUE!
I have no issues with single column average range, =AVERAGEIFS(AMR[[#All],[2002Feb]:[2002Feb]],AMR[[#All],[Act]],[@Act])
Once I now how to have 2 columns I{ll include more criteria and include variables. So pls use averageifS I have similar issue with sumifs by the way.
Thx a lot.

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### mart37

##### Well-known Member
You have to make 2 formula's and then teh average of this two formula's: average(average(...),average(...))

#### Eric W

##### MrExcel MVP
You're getting the #VALUE error because the value range (AMR[[#All],[2001Feb]:[2002Feb]]) is not the same size as the criteria range (AMR[[#All],[Act]]). The first is two columns, the next is 1 column.

Averaging 2 averages is not mathematically sound. In this case, where the number of items in each average is the same, then it works. But it's not a good habit to get into, and there's an easier way to do it anyway.

Book1
ABCDEFGHIJ
1Act2001Jan2002Feb2003MarActAverage
2ac226486661208568838Ac443847
3Pas129535012685101275926Pas1281930
4
Sheet2
Cell Formulas
RangeFormula
I2:I3I2=SUMPRODUCT(AMR[[2001Jan]:[2002Feb]]*(AMR[Act]=[@Act]))/SUMPRODUCT(SIGN(AMR[[2001Jan]:[2002Feb]])*(AMR[Act]=[@Act]))

The equivalent version of SUMIFS would not have the second SUMPRODUCT on the formula.

Edit: I just realized the use of SIGN to get the count only works if all the values are positive. If that's not the case, let me know and I'll come up with something else.

#### mart37

##### Well-known Member
replace SIGN with ISNUMBER

#### delfinpedro

##### New Member

ADVERTISEMENT

You're getting the #VALUE error because the value range (AMR[[#All],[2001Feb]:[2002Feb]]) is not the same size as the criteria range (AMR[[#All],[Act]]). The first is two columns, the next is 1 column.

Averaging 2 averages is not mathematically sound. In this case, where the number of items in each average is the same, then it works. But it's not a good habit to get into, and there's an easier way to do it anyway.

Book1
ABCDEFGHIJ
1Act2001Jan2002Feb2003MarActAverage
2ac226486661208568838Ac443847
3Pas129535012685101275926Pas1281930
4
Sheet2
Cell Formulas
RangeFormula
I2:I3I2=SUMPRODUCT(AMR[[2001Jan]:[2002Feb]]*(AMR[Act]=[@Act]))/SUMPRODUCT(SIGN(AMR[[2001Jan]:[2002Feb]])*(AMR[Act]=[@Act]))

The equivalent version of SUMIFS would not have the second SUMPRODUCT on the formula.

Edit: I just realized the use of SIGN to get the count only works if all the values are positive. If that's not the case, let me know and I'll come up with something else.
Thank you this combined with Mart 37´s contribution gave me the answer After this I included several indirects, etc, to automatice

#### delfinpedro

##### New Member
replace SIGN with ISNUMBER
Thank you this combined with Eric W´s contribution gave me the answer After this I included several indirects, etc, to automatice

#### Eric W

##### MrExcel MVP
Glad we could help!

Thanks for the update.

Replies
10
Views
481
Replies
8
Views
815
Replies
2
Views
102
Replies
8
Views
128
Replies
8
Views
744

Threads
1,129,380
Messages
5,635,918
Members
416,887
Latest member
SheriE

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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