# Subtotal combined with AverageIF

#### sTremper

##### New Member
Can you tell me how to use Subtotal and AverageIF? I want to subtotal the average but only positive numbers.

I can use Subtotal(Average... but I want Subtotal(AverageIF(xxx:xxx,">0").

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this array formula**:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A5,ROW(A5:A100)-ROW(A5),0,1))>0,A5:A100))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Adjust the range to suit.

Thanks for the reply. I will have to try it later -- past my bedtime.
Have a great and safe holiday weekend.
tks

I misunderstood your requirement.

Try this version:

=AVERAGE(IF(SUBTOTAL(9,OFFSET(A5,ROW(A5:A100)-ROW(A5),0,1))>0,A5:A100))

Still array entered!

Thanks T. Valko!
This site is great. Your code worked well. Thanks again.
Steve

You're welcome! Thanks for the feedback.

Well this is working correctly but now I have noticed that not all of my rows apply as they don't fit the criteria (e.g., row is only for data entry and not to be used in average calculation.) Is there a way to IF B2=X then ignore?

Replies
4
Views
112
Replies
2
Views
98
Replies
0
Views
88
Replies
6
Views
208
Replies
10
Views
229

1,203,506
Messages
6,055,805
Members
444,825
Latest member
aggerdanny

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

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