COLUMNS function
Quick answer
COLUMNS counts how many columns a range or array has, whether or not the cells contain anything.
- Syntax
=COLUMNS(array)- Most common use
=COLUMNS(B1:E1)returns 4 (Four months across)
It is handy for averaging across a row of months that includes blanks, checking how wide a lookup table is, and building column numbers that grow as a formula is filled to the right.
- Available in
- Every current version
- Category
- Lookup functions
- Returns
- The number of columns.
- Google Sheets
- Works the same way in Google Sheets.
Syntax
=COLUMNS(array)
| Argument | Required? | What it is |
|---|---|---|
array | Required | A range, an array, or a formula that returns an array. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | =COLUMNS(B1:E1) | 4 | Four months across |
| G3 | =SUM(B2:E2)/COLUMNS(B2:E2) | 42.75 | Average per month for North |
| G4 | =COLUMNS($B$1:C1) | 2 | A range that grows as you fill right: 2 by the second month |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Region | Jan | Feb | Mar | Apr | Result | |
| North | 41 | 38 | 45 | 47 | 4 | |
| South | 52 | 49 | 55 | 60 | 42.75 | |
| 2 |
Practice COLUMNS
Graded instantly, then re-checked on changed data.
Average over all months
H2, calculate the average spend per month over all six months: the row total divided by the number of month columns. Use COLUMNS, then fill down to H4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Expecting a count of filled cells | COLUMNS counts columns whether or not they hold data. Use COUNTA across the row to count filled cells. |
| Mixing up COLUMNS and COLUMN | COLUMN returns a column number, such as 3 for column C. COLUMNS returns how many columns a range spans. |
Tips
- In VLOOKUP, COLUMNS($A$1:B1) as col_index_num returns 2, then 3 and 4 as you fill right, so one formula pulls several columns.
Questions
How do I count the columns in a range?
=COLUMNS(A1:F1) returns 6, the number of columns in the range, whether they are filled or not.
How many columns does my table have?
=COLUMNS(Table1) returns the width of an Excel table, and =ROWS(Table1) returns the number of data rows.
Answers that use COLUMNS
In other languages
COLUMNS is COLUMNAS in Spanish Excel and SPALTEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .