Not totaling a hidden row

mlopes1

Board Regular
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.

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

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

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.

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.

Replies
0
Views
460
Replies
2
Views
611
Replies
1
Views
213
Replies
0
Views
142
Replies
1
Views
454

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.

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