AVERAGEIFS formula - adjust to exclude values of 0

joshdog13

New Member
Joined
Jan 10, 2022
Messages
2
Office Version
  1. 2016
I have a formula in column R of the example sheet that average returned values for multiple criteria being true in another tab. The formula works fine except I do not want it to average any 0's. For example the cell in the row for "Derek" should return an average of $15,345 if the one 0 he has is not included in the average.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
I tried including a screenshot but get an error message the image is too large. How else can I share the image or file?
 
Upvote 0
You can use XL2BB to post a small sample.

See if this example helps:
Book1
ABCDE
1Average with out 0
2Derek12Derek16.667
3Derek0
4Sally22
5Sally25
6Derek12
7Jim25
8Derek26
Sheet1
Cell Formulas
RangeFormula
E2E2=AVERAGEIFS($B$2:$B$8,$A$2:$A$8,$D$2,$B$2:$B$8,"<>0")
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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