Sum across worksheets while ignoring hidden worksheets

Hello,

I’m trying to figure out how I can do a SUM formula across worksheets and ONLY count the cells on the worksheets that are visible and ignore the cells when the worksheet is hidden.

In the attached – the “cover sheet” tab cells B10:B15 are designed to allow users the ability to hide or unhide cells. Straightforward Yes/No selection.

The formulas in K12:K17 on the same tab count the corresponding cells on all the remaining worksheets F1:F7 (except F2). I’d like this formula to ignore the corresponding cells when the worksheet is hidden.

Example – I select NO in cell B12 on the Cover Sheet Sheet Tab. This hides the Call Center tab from view. I don’t want any of the data in the call center tab cells F1:F7 to be counted on the Cover Sheet tab.

I can’t use VBA code as we have users who are using MAC devices –

Thanks

Thanks, Carin,

I'll admit my skillset in names is probably not as good as it should be. Would names allow the calculation to be dynamic in the sense that hiding and un-hiding a worksheet is selectable by the user.

Thanks Carim,

This looks really good. One issue that I found is on the cover tab - when I select YES for Session Manager in cell B13 the corresponding worksheet does not appear but when I select YES for Survivability in Cell B12 I get both Survivability and Session Manager worksheets to appear. The calculations in row K are working

Thanks. One last question if you don't mind. Attached is the latest copy of the workbook - some of the row numbers changed on the coversheet tab due to formatting and feedback. I updated the code best I can using what you sent. The only thing that I can't get to work is the calculations in column K. I copied your formula over and hit CTRL-Shift-enter to make in an array and did a steer and compare - I can't find the place to update.

Thanks - I was just looking at that - hopefully this will be all. If we end up adding more worksheets - thus more test plan results on the cover sheet, what do I need to modify in the formula in column K?