How do i total value in visible cells only (ignore hiddens)

rsclark

Board Regular
Joined
May 7, 2004
Messages
101
I have a detailed list of items in column A. There are associated values in column B. Many of the values are zero. On each row i have a button that enables the user to hide the row at their discretion. Normally, they will hide the rows with zeros, but sometimes they may want to leave them in. I want to show a total of the visible values at the bottom. This causes a problem if the user hides a row with a non-zero value as the output will appear wrong. Is it possible to make the total only add visible values and ignore any values in hidden rows.

Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

rsclark

Board Regular
Joined
May 7, 2004
Messages
101
I thought subtotal only worked with filtered data and not hidden rows. I have just tried using subtotal but it still adds in values included in hidden rows. Or, is there an option that i am missing. (i am using Excel 97)
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Check the help file. It contains a detailed explanation on when hidden values are included and when they are excluded.
 
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,163,809
Messages
5,833,790
Members
430,233
Latest member
cbutts

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