Average Multiple Columns and Rows with specific criteria

[Mathsy]

Hi,

Please give me a hand here! I'm trying to average a lot of data, but only if it fits certain criteria.

I have data for different items, split across different months. And each row is given a category
So, for example:

Item	Category	Jan	Feb	March	April
Orange	Fruit	3	6	0	8
Apple	Fruit	8	0	0	2
Cabbage	Vegetable	0	0	3	8
Banana	Fruit	9	8	8	8
Potato	Vegetable	8	6	6	0
Chair	Furniture	9	7	0	0

What I'm trying to do is find the average for each category, excluding the 0s. So, I'd want the average for fruit, (so the orange, apple and banana columns), but only if the value is not 0. I've tried to do an averageif formula and all sorts but it won't work with the categories.

Really need to get this done so would appreciate any advice!

 

[jasonb75]

Is this what you're trying to do?

Book1
	A	B	C	D	E	F
1	Item	Category	Jan	Feb	March	April
2	Orange	Fruit	3	6	0	8
3	Apple	Fruit	8	0	0	2
4	Cabbage	Vegetable	0	0	3	8
5	Banana	Fruit	9	8	8	8
6	Potato	Vegetable	8	6	6	0
7	Chair	Furniture	9	7	0	0
8
9		Fruit	6.666667	7	8	6
10		Vegetable	8	6	4.5	8
11		Furniture	9	7	0	0
Sheet5

Cell Formulas
Range	Formula
B9:B11	B9	=UNIQUE(B2:B7)
C9:F11	C9	=IFERROR(AVERAGEIFS(C2:C7,$B$2:$B$7,$B9#,C2:C7,"<>0"),0)
Dynamic array formulas.

 

[Mathsy]

Is this what you're trying to do?

Book1
	A	B	C	D	E	F
1	Item	Category	Jan	Feb	March	April
2	Orange	Fruit	3	6	0	8
3	Apple	Fruit	8	0	0	2
4	Cabbage	Vegetable	0	0	3	8
5	Banana	Fruit	9	8	8	8
6	Potato	Vegetable	8	6	6	0
7	Chair	Furniture	9	7	0	0
8
9		Fruit	6.666667	7	8	6
10		Vegetable	8	6	4.5	8
11		Furniture	9	7	0	0
Sheet5

Cell Formulas
Range	Formula
B9:B11	B9	=UNIQUE(B2:B7)
C9:F11	C9	=IFERROR(AVERAGEIFS(C2:C7,$B$2:$B$7,$B9#,C2:C7,"<>0"),0)
Dynamic array formulas.

Thank you! Similar but not quite - I want an average for all of the months, so an average for the fruit but for January through April, excluding the 0s.

So the average for c2:f7, if column b is fruit, and if <>0

I know I could average each individual row and then average them all, but that would only get the average of the average rather than the average, if that makes sense?

 

[jasonb75]

but that would only get the average of the average rather than the average, if that makes sense?

Perfect sense, it's amazing how many people that ask similar questions don't understand how that works.

This should do what you need, remember to array confirm the formula if you're not using office 365.

Book1
	A	B	C	D	E	F
1	Item	Category	Jan	Feb	March	April
2	Orange	Fruit	3	6	0	8
3	Apple	Fruit	8	0	0	2
4	Cabbage	Vegetable	0	0	3	8
5	Banana	Fruit	9	8	8	8
6	Potato	Vegetable	8	6	6	0
7	Chair	Furniture	9	7	0	0
8
9		Fruit	6.666667
10		Vegetable	6.2
11		Furniture	8
Sheet5

Cell Formulas
Range	Formula
B9:B11	B9	=UNIQUE(B2:B7)
C9:C11	C9	=AVERAGE(IF($B$2:$B$7=B9,IF($C$2:$F$7<>0,$C$2:$F$7)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[Mathsy]

Perfect sense, it's amazing how many people that ask similar questions don't understand how that works.

This should do what you need, remember to array confirm the formula if you're not using office 365.

Book1
	A	B	C	D	E	F
1	Item	Category	Jan	Feb	March	April
2	Orange	Fruit	3	6	0	8
3	Apple	Fruit	8	0	0	2
4	Cabbage	Vegetable	0	0	3	8
5	Banana	Fruit	9	8	8	8
6	Potato	Vegetable	8	6	6	0
7	Chair	Furniture	9	7	0	0
8
9		Fruit	6.666667
10		Vegetable	6.2
11		Furniture	8
Sheet5

Cell Formulas
Range	Formula
B9:B11	B9	=UNIQUE(B2:B7)
C9:C11	C9	=AVERAGE(IF($B$2:$B$7=B9,IF($C$2:$F$7<>0,$C$2:$F$7)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Thank you! This worked perfectly!!!