# Average Multiple Columns and Rows with specific criteria

#### Mathsy

##### New Member
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!

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Is this what you're trying to do?
Book1
ABCDEF
1ItemCategoryJanFebMarchApril
2OrangeFruit3608
3AppleFruit8002
4CabbageVegetable0038
5BananaFruit9888
6PotatoVegetable8660
7ChairFurniture9700
8
9Fruit6.666667786
10Vegetable864.58
11Furniture9700
Sheet5
Cell Formulas
RangeFormula
B9:B11B9=UNIQUE(B2:B7)
C9:F11C9=IFERROR(AVERAGEIFS(C2:C7,\$B\$2:\$B\$7,\$B9#,C2:C7,"<>0"),0)
Dynamic array formulas.

Is this what you're trying to do?
Book1
ABCDEF
1ItemCategoryJanFebMarchApril
2OrangeFruit3608
3AppleFruit8002
4CabbageVegetable0038
5BananaFruit9888
6PotatoVegetable8660
7ChairFurniture9700
8
9Fruit6.666667786
10Vegetable864.58
11Furniture9700
Sheet5
Cell Formulas
RangeFormula
B9:B11B9=UNIQUE(B2:B7)
C9:F11C9=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?

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
ABCDEF
1ItemCategoryJanFebMarchApril
2OrangeFruit3608
3AppleFruit8002
4CabbageVegetable0038
5BananaFruit9888
6PotatoVegetable8660
7ChairFurniture9700
8
9Fruit6.666667
10Vegetable6.2
11Furniture8
Sheet5
Cell Formulas
RangeFormula
B9:B11B9=UNIQUE(B2:B7)
C9:C11C9=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.

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
ABCDEF
1ItemCategoryJanFebMarchApril
2OrangeFruit3608
3AppleFruit8002
4CabbageVegetable0038
5BananaFruit9888
6PotatoVegetable8660
7ChairFurniture9700
8
9Fruit6.666667
10Vegetable6.2
11Furniture8
Sheet5
Cell Formulas
RangeFormula
B9:B11B9=UNIQUE(B2:B7)
C9:C11C9=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!!!

Replies
14
Views
426
Replies
2
Views
116
Replies
6
Views
170
Replies
4
Views
117
Replies
3
Views
192

1,214,713
Messages
6,121,042
Members
449,006
Latest member
cthorne1

### 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.

### Which adblocker are you using?

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

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