Solved

Excel count columns in a range

Posted on 2011-10-07
261 Views
I need an efficient and preferably non-volatile worksheet function to count the columns in a supplied range.  I am using e.g.
``````=COUNTA(\$A\$1:\$D\$1)+COUNTBLANK(\$A\$1:\$D\$1)
``````
for a range that has only one row, or I guess I could have
``````=COUNTA(INDEX(\$A\$1:\$E\$22,1,))+COUNTBLANK(INDEX(\$A\$1:\$E\$22,1,))
``````
for a range with multiple rows.

Obviously I could have a simple UDF like
``````Public Function ColCount(ByRef rngIn As Excel.Range) As Long
ColCount = rngIn.Columns.Count
End Function
``````
to do this, but I'm looking for a worksheet function if possible.

Any better ideas than mine?
0
Question by:andrewssd3

LVL 50

Accepted Solution

Try using COLUMNS function

=COLUMNS(range)

regards, barry
0

LVL 33

Expert Comment

Do you mean the no of columns with data?

If you don't then you can just use COLUMNS which will return the no of columns in a range.
0

LVL 50

Expert Comment

Just a note on that first formula you listed

=COUNTA(\$A\$1:\$D\$1)+COUNTBLANK(\$A\$1:\$D\$1)

You have to be careful with that if any of the cells contains "formula blanks" (null strings) as both COUNTA and COUNTBLANK count those....so it's possible that that formula could give you a result of 4,5,6,7 or 8!

regards, barry
0

Featured Post

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.