COLUMNS function
COLUMNS counts how many columns a range or array has, whether or not the cells contain anything. 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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.