Count number of columns containing data

[bkalma]

I would like to write a formula which counts the total number of columns which contain data in a table. For example:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]


The formula should return a value of 4 for the above table as the number of columns containing data is 4. The actual table will contain random values; not just 1.​

 

[admiral100]

Hi,

try this - Ctrl+Shift+Enter NOT just Enter

=SUM(IF(FREQUENCY(IF(A1:E5<>"",COLUMN(A1:E5)-COLUMN(A1)+1),COLUMN(A1:E5)-COLUMN(A1)+1),1))

 

[bkalma]

Works perfectly. Thanks!

 

[admiral100]

You are welcome