Averaging with two conditions in a table; Horizontal & Vertical

[oman3k]

I am struggling to work out an average in a table with two conditions, I have tried averageifs and sumproduct and failed, please help.

E.g. I want to know the average of all Cat that are in the UK only, which would be 3.33, or how many Man in China which should average 2. Please help with a formula.

Location	Cat	Dog	Man
UK	3	5	2
UK	5	3	2
China	2	1	2
USA	1	2	3
China	3	4	2
UK	2	3	2

<tbody>
</tbody>

 

[Eric W]

Welcome to the board.

Try:

	A	B	C	D	E	F	G	H
1	Location	Cat	Dog	Man		Location	Type	Average
2	UK	3	5	2		UK	Cat	3.333333
3	UK	5	3	2
4	China	2	1	2
5	USA	1	2	3
6	China	3	4	2
7	UK	2	3	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =SUMPRODUCT(B2:D7*(A2:A7=F2)*(B1:D1=G2))/SUMPRODUCT((A2:A7=F2)*(B1:D1=G2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Misca]

Welcome to the msg board!

I've put your table on A1:D7 and the criteria in G2 (Location) and H2 (Item) and here's my formula:

=SUMPRODUCT(--(A2:A7=G2);INDEX(B2:D7;;MATCH(H2;B1:D1;0)))/SUMPRODUCT(--(A2:A7=G2))

The first part is summing the rows where the location matches the criteria. The INDEX/MATCH finds the right column to sum. The second part only counts the number of rows with the matching location criteria.

The formula only works if there's no duplicates in the column names.

 

[oman3k]

Eric, thank you, that works, however, I have got some blank cells too which I want the calculation to ignore, e.g. for "UK" there may be some blank cells, but your calculation still factors in the blank cells that I wish to omit, is there a solution?

Many thanks

Welcome to the board.

Try:

	A	B	C	D	E	F	G	H
1	Location	Cat	Dog	Man		Location	Type	Average
2	UK	3	5	2		UK	Cat	3.333333
3	UK	5	3	2
4	China	2	1	2
5	USA	1	2	3
6	China	3	4	2
7	UK	2	3	2

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =SUMPRODUCT(B2:D7*(A2:A7=F2)*(B1:D1=G2))/SUMPRODUCT((A2:A7=F2)*(B1:D1=G2)) <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

Location	Cat	Dog	Man
UK	3	5	2			UK	cat	3.33333
UK	5	3	2
China	2	1	2
USA	1	2	3
China	3	4	2
UK	2	3	2

<tbody>
</tbody>

In I2 enter:

=AVERAGEIFS(INDEX($B$2:$D$7,0,MATCH(H2,$B$1:$D$1,0)),$A$2:$A$7,$G2)

 

[Eric W]

You could adjust my formula to:

=SUMPRODUCT(B2:D7*(A2:A7=G2)*(B1:D1=H2))/SUMPRODUCT((B2:D7<>"")*(A2:A7=G2)*(B1:D1=H2))

but I suspect that Aladin's formula would be more efficient.

 

[oman3k]

You could adjust my formula to:

=SUMPRODUCT(B2:D7*(A2:A7=G2)*(B1:D1=H2))/SUMPRODUCT((B2:D7<>"")*(A2:A7=G2)*(B1:D1=H2))

but I suspect that Aladin's formula would be more efficient.

Thank you all for the formulas, I got them all to work just fine.

i plumped for Eric,s solution at the end as I understood it easier, buts huge thanks to you all for the solutions.

 

[Umareddy]

thank you so much - I was exactly looking for this formula