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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,874
Messages
6,104,509
Members
447,913
Latest member
kowboy_sparrow

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