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

[delfinpedro]

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.

 

[mart37]

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

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J
1			Act	2001Jan	2002Feb	2003Mar		Act	Average
2			ac	226486	661208	568838		Ac	443847
3			Pas	1295350	1268510	1275926		Pas	1281930
4
Sheet2

Cell Formulas
Range	Formula
I2:I3	I2	=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]

replace SIGN with ISNUMBER

 

[delfinpedro]

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
	A	B	C	D	E	F	G	H	I	J
1			Act	2001Jan	2002Feb	2003Mar		Act	Average
2			ac	226486	661208	568838		Ac	443847
3			Pas	1295350	1268510	1275926		Pas	1281930
4
Sheet2

Cell Formulas
Range	Formula
I2:I3	I2	=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]

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]

Glad we could help!

Thanks for the update.