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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,028
Check the help file. It contains a detailed explanation on when hidden values are included and when they are excluded.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,539
Messages
5,572,786
Members
412,484
Latest member
deezina07
Top