# Exclude Hidden Rows from being used within a formula

#### Hughy649

##### New Member
Good afternoon

My array is all data, some of it, at times, I want to hide and not be included in the calculations. I searched the internet and I believe that Subtotal should work to hide any hidden rows from being included in a formula. However, I can't figure out where to include in it the formula - front, within, the array itself?!?

I use excel 2010 and the formulas are below. Any help would be greatly appreciated.

Cheers

Andy

=SMALL(BJ108:BJ117,COUNTIF(BJ108:BJ117,0)+1) - finding the smallest # not including zero's

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2) - finding the weighted average

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### MrExcel MVP
Good afternoon

My array is all data, some of it, at times, I want to hide and not be included in the calculations. I searched the internet and I believe that Subtotal should work to hide any hidden rows from being included in a formula. However, I can't figure out where to include in it the formula - front, within, the array itself?!?

I use excel 2010 and the formulas are below. Any help would be greatly appreciated.

Cheers

Andy

=SMALL(BJ108:BJ117,COUNTIF(BJ108:BJ117,0)+1) - finding the smallest # not including zero's

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2) - finding the weighted average

Control+shift+enter, not just enter:
Rich (BB code):
``````=SMALL(IF(SUBTOTAL(2,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),0,1)),
IF(BJ108:BJ117>0,BJ108:BJ117)),2)
``````

Assuming that the autofilter does not include columns AV, AX, and AY...
Rich (BB code):
``=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2)``
becomes
Rich (BB code):
``````=SUBTOTAL(9,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),0,1))-
AX111-AY111)/(AV109-2)
``````

#### Andrew Poulsom

##### MrExcel MVP
For you first formula you could also try:

=AGGREGATE(15,5,BJ108:BJ117,SUBTOTAL(102,BJ108:BJ117)-SUMPRODUCT(--(SUBTOTAL(109,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),,1,1))<>0))+1)

which is a normal formula, not an array formula.

#### Hughy649

##### New Member
For you first formula you could also try:

=AGGREGATE(15,5,BJ108:BJ117,SUBTOTAL(102,BJ108:BJ117)-SUMPRODUCT(--(SUBTOTAL(109,OFFSET(BJ108,ROW(BJ108:BJ117)-ROW(BJ108),,1,1))<>0))+1)

which is a normal formula, not an array formula.

Thank you very much gentlemen - I will give them a try!

#### Hughy649

##### New Member
Thanks again gents.

I got yours to work perfectly Andrew - absolutely amazing!

This is more complicated than anticipated.

I got one other formula to work, being: =Max(F20:F60) which is now =AGGREGATE(15,5,F20:F60,SUBTOTAL(102,F20:F60))+1

However the weighted average eludes me, which is:

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2)

As always, any assistance would be greatly appreciated.

Andy

##### MrExcel MVP
Thanks again gents.

I got yours to work perfectly Andrew - absolutely amazing!

This is more complicated than anticipated.

I got one other formula to work, being: =Max(F20:F60) which is now =AGGREGATE(15,5,F20:F60,SUBTOTAL(102,F20:F60))+1

However the weighted average eludes me, which is:

=(SUM(BJ108:BJ117)-AX111-AY111)/(AV109-2)

As always, any assistance would be greatly appreciated.

Andy

If you are doing manual hiding, replace 2 of SUBTOTAL with 102.

#### Andrew Poulsom

##### MrExcel MVP
Try replacing SUM(BJ108:BJ117) with SUBTOTAL(109,BJ108:BJ117).

#### Andrew Poulsom

##### MrExcel MVP
Also for MAX:

=SUBTOTAL(109,F20:F60)

#### Hughy649

##### New Member
Thanks Andrew

=((SUBTOTAL(109,G20:G60))-O8-P8)/(M\$4-2) Worked perfectly.

And thanks for the second max formula.

Cheers

#### Hughy649

##### New Member
One last issue, being counting the number of unhidden cells in range S20:S54, with values over 0.

This looked like it should work but to no avail.

=SUMPRODUCT(SUBTOTAL(103,OFFSET(S20:S54,ROW(S20:S54)-ROW(S20),0,1)),--(S20:S54>0))

Andy

Replies
0
Views
512
Replies
4
Views
429
Replies
0
Views
306
Replies
12
Views
1K
Replies
6
Views
1K

1,196,021
Messages
6,012,906
Members
441,740
Latest member
Latrs

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