Contents
Yesterday in the marathon 30 Excel functions in 30 days we got detailed information about the cell using the function CELL (CELL) and found that it is quite useful in some cases, for example to get the name of a sheet.
On the 12th day of the marathon, we will study the function COLUMNS (NUMBERCOLUMN). Would this feature be just as useful? Or is it just another lazy function like AREAS (AREAS)? As promised, she counts the number of columns, and she is also an excellent team player, but you will not find any exotics or superpowers here.
So let’s take a closer look at the function COLUMNS (NUMBERCOLUMN), as well as examples of its use. If you have additional information or your own examples, please share them in the comments.
Function 12: COLUMNS(COLUMN)
Function COLUMNS (COLUMN) returns the number of columns in an array or reference.
How can you use the COLUMNS(COLUMN) function?
Function COLUMNS (NUMBERCOLUMN) can show the size of a table or named range:
- Counts columns in an Excel table.
- Summarizes the last column in the named range.
Syntax COLUMNS(COLUMN)
Function COLUMNS (NUMBERCOLUMN) has the following syntax:
COLUMNS(array)
ЧИСЛСТОЛБ(массив)
- array (array) is an array, array formula, or range reference.
Traps COLUMNS(NUMBERCOLUMN)
If you are using a range reference, then it must be an adjacent range of cells.
Example 1: Counting columns in an Excel spreadsheet
In Excel 2007 and Excel 2010, you can create a table using the command Table (Table) and refer to its name in the formula. This example shows a table called RegionSales.
Function COLUMNS (COLUMN) counts the number of columns in this table.
=COLUMNS(RegionSales)
=ЧИСЛСТОЛБ(RegionSales)
Example 2: Sum the last column in a named range
If you use the function COLUMNS (NUMBERCOLUMN) combined with SUM (SUM) and INDEX (INDEX), you can get the total for the last column in the named range. In our example, this is a range named MyRange.
The following formula sums the last column in the named range:
=SUM(INDEX(MyRange,,COLUMNS(MyRange)))
=СУММ(ИНДЕКС(MyRange;;ЧИСЛСТОЛБ(MyRange)))