Counting Non-Hidden Cells in a Row

[dbrown14]

I need to be able to count the number of cells in a row that contain numbers, but exclude the hidden columns. Here is a crude example:

Row1: 5 7 4 6 8 10 Count=6
Now I hide two rows
Row1: 5 7 8 10 Count=4


Any help is appreciated but no macros please. I need this to be as user friendly as possible. I also know that SUBTOTAL works great with this when the data is in a column but it doesn't want to work when I use it on a row like the example.

 

[ISY]

Hallo

Example of a formula with the Italian text to be converted

=SE(CELLA("larghezza";A12)=0;0;A12)+SE(CELLA("larghezza";B12)=0;0;B12)

This wants to be an example to be completed for the next column!

Excuse the incomplete formula!

 

[dbrown14]

I understand your algorithm and tried it in the form of this equation:

{=SUM(IF(CELL("width",A1:J1)>0,1))}

but the result is always "2". I'm not expert at excel so if my formula is off please help me and any other suggestions are welcome.

 

[ISY]

Hallo
I forgot to update you recalculate the spreadsheet formulas with F9

 

[dbrown14]

The number stays the same when I do that.

And I don't understand why AGGREGATE and SUBTOTAL have the option to ignore hidden rows but not hidden columns.

 

[ISY]

Hallo

I confirm that I sent you the formula that works regularly
Retry

 

[dbrown14]

I put your exact code into my cell where the count is supposed to be and I got the sum of all the visible cells. Not the count of how many cells are visible

 

[T. Valko]

I put your exact code into my cell where the count is supposed to be and I got the sum of all the visible cells. Not the count of how many cells are visible

That formula won't work.

You would need to put a formula like this in a row of helper cells.

Let's assume row 1, A1:J1.

=CELL("width",A1)

Then, in some cell, say cell L1, enter this formula:

=COUNTIF(A1:J1,">0")

However, hiding/unhiding a column does not trigger a calculation so you would have to do a manual calculation to get the formula to update or you'd have to wait until some other event triggered a calculation.

This is probably not what you had in mind so you may want to reconsider why you're doing this to begin with.