I have a worksheet with a specific character (E.g. "X") in various cells along a row. The user has the option to hide the columns that do not apply to them (a button linked to a macro that hides the selected columns). Some of the hidden columns may contain the "X".
I am trying to use COUNTIF, COUNTA or a variation of these to count the number of cells that contain "X" in the range(D13:AR13), but ignoring the columns that have been hidden by the macro.
I have tried several suggested solutions on EE but either it ignores the hidden columns and just gives me the total number of occurrences of "X", or the wrong answer (For example, when I use =COUNTA(103,D13:AR13, I get 8 (there are actually only 7 "X"s). I have also tried using user defined functions, but these throw up 'Invalid name" errors.
I could probably do this using a macro that recounts the number of "X"s every time columns are hidden or unhidden but I'd prefer a simpler solution. Surely, it can't be that difficult to count the number of cells that contain a character, but ignore hidden columns?
The solution, whether macro driven or a formula, needs to be dynamic. I.e. when the user hides columns the new count appears instantly.
Any help greatly appreciated.