Why isn't SUBTOTAL doing what is says on the tin?

Mhowells

New Member
Joined
Aug 24, 2006
Messages
29
Dear all,

I am trying to write a formula that sums up only the visible cells in a worksheet, so that the sum only represents autofiltered data. In theory, SUBTOTAL should do this, but I find the formula I have written continues to return the sum of teh entire column, with the hidden cells included.

The formula is as follows:

=SUBTOTAL(9,(OFFSET($F$1,1,0)):(INDIRECT(CONCATENATE("F",(COUNTA(OFFERCOUNT)+1)))))

'(COUNTA(OFFERCOUNT)+1)' returns the row number of the last row of data (which changes). OFFERCOUNT is a defined named range....


Can anyone tell me what i am doing wrong?

With best wishes,
Max
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi,

When trying your formula, it works as you request. I only get sum of visible cells as long as they are hidden by auto filter.

FWIW

=SUBTOTAL(9,$F$2:INDEX(F:F,COUNTA(OFFERCOUNT)+1))

might be a simpler and more efficient formula.
 
Upvote 0
Thanks Fairwinds....

Annoyingly neither this nor your formula have the desired result on my system. I have experimented with a couple of potential reasons for this but cannot really see what is going on. I am running Office 97 on an NT4 sytem, and wonder if perhaps this is a feature of an older version of office.

Any suggestions as to why this works on some machines and not others would be much appreciated... Else, is there an alternative method to get this result? Strangely, my formula is based on a more complex version of SUBTOTAL that actually works on here, so why this one doesn't is entirely beyond me.

Cheers,
Max
 
Upvote 0
Oh I'm pleased... Not that it doesn't work, but that there is actually a flaw which prevents this from working.

Thanks for that Fairwinds.

Best,
Max
 
Upvote 0

Forum statistics

Threads
1,226,713
Messages
6,192,628
Members
453,742
Latest member
Fleeeee

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