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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 

Mathsy

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Mathsy

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,237
Messages
5,836,181
Members
430,406
Latest member
pmav

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
Top