Average non-adjacent cells (with criteria)

[MonicaK]

Hi,

I am stuck on creating a formula for the below and would be really grateful for some assistance:

The table has 4 columns. Column A is text e.g. Apples, Bananas etc. Columns B,C,D are currency. More than one row in column A will contain Apples.

I want to find all rows which state Apple then sum the corresponding figures in columns B and D only and divide that sum by the count of Apples to obtain a correctly averaged figure.

I looked at averageif but couldn’t figure out how to use column B and D without C, and I wasn’t sure if the average would calculate correctly.

Any ideas?

Thanks,

Monica

 

[Peter_SSs]

To simplify matters, using the earlier example table of A to D if it now had a column E populated with either Frog or Goat then the next formula I need should calculate the average of B and D where E=Frog and A=Apple.

23 07 09.xlsm
	A	B	C	D	E	F	G	H	I
1	Fruit	Amt1	Amt2	Amt3	Animal		Fruit	Animal	Average
2	Apple	1	2	3	Goat		Apple	Frog	18
3	Pear	2	4	5	Goat
4	Apple	3	6	7	Frog
5	Banana	4	8	11	Frog
6	Cherry	5	10	13	Frog
7	Banana	6	12	17	Goat
8	Apple	7	14	19	Frog
9
Average

Cell Formulas
Range	Formula
I2	I2	=LET(f,FILTER(CHOOSECOLS(Table1,2,4),(Table1[Fruit]=G2)*(Table1[Animal]=H2)),SUM(f)/ROWS(f))

 

[MonicaK]

23 07 09.xlsm
	A	B	C	D	E	F	G	H	I
1	Fruit	Amt1	Amt2	Amt3	Animal		Fruit	Animal	Average
2	Apple	1	2	3	Goat		Apple	Frog	18
3	Pear	2	4	5	Goat
4	Apple	3	6	7	Frog
5	Banana	4	8	11	Frog
6	Cherry	5	10	13	Frog
7	Banana	6	12	17	Goat
8	Apple	7	14	19	Frog
9
Average

Cell Formulas
Range	Formula
I2	I2	=LET(f,FILTER(CHOOSECOLS(Table1,2,4),(Table1[Fruit]=G2)*(Table1[Animal]=H2)),SUM(f)/ROWS(f))

Sorry Peter, I missed this whilst banging my head against a brick wall! ha ha!

It works! Thank you so much for your help - you are a genius

Kind regards,

Monica

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[wk9128]

Pls try

Cell I formula

=SUM(SUMIFS(OFFSET(B2:B8,,{0,2}),A2:A8,G2,E2:E8,H2))/COUNTIFS(A2:A8,G2,E2:E8,H2)

 

[MonicaK]

You're welcome. Thanks for the follow-up.

Sorry Peter, me again! Your formula below worked:

=LET(f,FILTER(CHOOSECOLS(Table1,2,4),(Table1[Fruit]=G2)*(Table1[Animal]=H2)),SUM(f)/ROWS(f))

However, I now need to modify it to average the results from 3 tables (3 months in reality). Table 1 (ie. April) is on sheet A, table 2 (ie. May) is on Sheet 2 and table 3 (ie. June) is on sheet 3. The tables are set out identically on each sheet.
E.g. still using if Fruit = G2 and Animal=H2 and sum columns 2 and 4 but now of all 3 tables the divide it by the sum of rows of all 3 tables to obtain the average.

Hopefully this is not too tricky

Thanks,

Monica

 

[Peter_SSs]

Sounds like not an ideal layout. All the data in a single table with a Date and/or Month column would be a lot simpler.
However, you could try this.

=LET(f,FILTER(CHOOSECOLS(Table1,2,4),(Table1[Fruit]=G2)*(Table1[Animal]=H2)),g,FILTER(CHOOSECOLS(Table2,2,4),(Table2[Fruit]=G2)*(Table2[Animal]=H2)),h,FILTER(CHOOSECOLS(Table3,2,4),(Table3[Fruit]=G2)*(Table3[Animal]=H2)),SUM(f,g,h)/(ROWS(f)+ROWS(g)+ROWS(h)))

 

[MonicaK]

Sounds like not an ideal layout. All the data in a single table with a Date and/or Month column would be a lot simpler.
However, you could try this.

=LET(f,FILTER(CHOOSECOLS(Table1,2,4),(Table1[Fruit]=G2)*(Table1[Animal]=H2)),g,FILTER(CHOOSECOLS(Table2,2,4),(Table2[Fruit]=G2)*(Table2[Animal]=H2)),h,FILTER(CHOOSECOLS(Table3,2,4),(Table3[Fruit]=G2)*(Table3[Animal]=H2)),SUM(f,g,h)/(ROWS(f)+ROWS(g)+ROWS(h)))

No, not the best layout but it’s monthly reports in different workbooks and I can’t change that. Thanks for the formula Peter but unfortunately if there is no match on one of the tables the formula doesn’t work. Not sure why because the formula sums f,g,h and divides by the sum of the f,g,h rows. Odd!

 

[MonicaK]

Don’t worry Peter, for ease of calculation I have merged the 3 reports into 1 and used your original formula again. Thanks for your assistance though.

 

[Peter_SSs]

if there is no match on one of the tables the formula doesn’t work.

for ease of calculation I have merged the 3 reports into 1 and used your original formula again.

That sounds like a much neater solution but the problem *could* have been avoided with this even longer formula

=LET(f,FILTER(CHOOSECOLS(Table1,2,4),(Table1[Fruit]=G2)*(Table1[Animal]=H2),NA()),g,FILTER(CHOOSECOLS(Table2,2,4),(Table2[Fruit]=G2)*(Table2[Animal]=H2),NA()),h,FILTER(CHOOSECOLS(Table3,2,4),(Table3[Fruit]=G2)*(Table3[Animal]=H2),NA()),SUM(IFNA(f,0),IFNA(g,0),IFNA(h,0))/(IFNA(ROWS(f),0)+IFNA(ROWS(g),0)+IFNA(ROWS(h),0)))

Thanks for your assistance though.

You are very welcome.