Average Multiple Columns and Rows with specific criteria

Mathsy

New Member
Joined
Oct 20, 2021
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. MacOS
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:

ItemCategoryJanFebMarchApril
OrangeFruit3608
AppleFruit8002
CabbageVegetable0038
BananaFruit9888
PotatoVegetable8660
ChairFurniture9700


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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Solution
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!!!
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top