Not totaling a hidden row

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
I have a worksheet that shows a list of products and their costs. Sometimes, for printing purposes, I don't want to see certain rows, but I don't want to delete them, since next time I may want to have that product on the printout. However, when I hide it, the total cost that is summed at the bottom still adds hidden rows in. Is there a way to have it only total visible columns? Thank you as always,


Mark
This message was edited by mlopes1 on 2002-09-06 10:49
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Paul_Godley

New Member
Joined
Sep 5, 2002
Messages
18
You can use =SUBTOTAL(9,Range_To_Add) instead of =SUM(Range_To_Add) as long as the rows are hidden by filtering not just hidden manually.
 

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
Paul,

that worked but just out of curiosity what is the 9 for in the formula? I was playing around and changed it to 8 and got some decimal answer. In case I need to use this again, I was curious what the number means. Thanks for your help.


Mark
 

hedrijw

Board Regular
Joined
Jun 17, 2002
Messages
210

ADVERTISEMENT

It's the function number to use

Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
On 2002-09-06 11:17, mlopes1 wrote:
Paul,

that worked but just out of curiosity what is the 9 for in the formula? I was playing around and changed it to 8 and got some decimal answer. In case I need to use this again, I was curious what the number means. Thanks for your help.


Mark

Just consult the Help file to find out, & try to be more specific when you post a problem. You made me search for code, just because you gave the impression that your hidden rows were not due to filtering.
 

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
You are right, I will look in help file. But about being more specific in my question; originally, the impression you got was correct, my rows being hidden wasmt wasnt due to filtering. But after reading Paul's suggestion, I hid them using filtering so that his code would work.

Next time I will make sure to include the fact that how I currently hide them does not need to be the method I use in the solution. Thanks for your suggestions, this board is always very helpful.
 
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,164,613
Messages
5,838,378
Members
430,541
Latest member
Chopin1976

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