Excel 2016 Total or Subtotal Row ignoring hidden columns

[Chris Waller]

Hi all,

I am trying to create a subtotal across a row like you would down a column, however, when you try to subtotal a row the calculation appears to include the hidden information.

I have a Spreadsheet that contains numerous macros which hide\unhide columns of data, however, when I try to create a total or subtotal of the rows of data the hidden data are always included in the total, unlike when you are subtotalling a column of data when it only totals the visible figures.

Does anyone know if you can use a formula to do this kind of calculation or would it be better to do this using VBA? If so, could someone please let me know how I should go about this? TIA

 

[Roger Govier]

Hi
Whilst Hiding or Unhiding your columns, could you get your macro to put a letter "H" for example in row 1?
Then you could use something like
=SUMIFS(C3:S3,$C$1:$S$1,"<>H")

 

[Chris Waller]

=SUMIFS(C3:S3,$C$1:$S$1,"<>H")

Roger,

Thanks for the formula above. I have just tried it and unfortunately it doesn't appear to work. I may be doing something wrong and I'll give it another go.

 

[Peter_SSs]

Thanks for the formula above. I have just tried it and unfortunately it doesn't appear to work.

It wasn't a stand-alone formula suggestion. It also included ..

Whilst Hiding or Unhiding your columns, could you get your macro to put a letter "H" for example in row 1?

Did you alter your macro to do something like that? (You may need to insert a new row and hide it to preserve the look of your sheet)