COLUMNS counts the columns spanned by a range, including blank columns. Use COLUMN instead when you need a cell’s column position.
COLUMNS function syntax
=COLUMNS(range)- range: reference or array whose width you want to count.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Region | Jan | Feb |
|---|---|---|
| East | 120 | 160 |
| West | 90 | 110 |
Count columns in a range
Enter this formula in A10. The range is three columns wide. Its row count and the number of filled cells do not change that width.
=COLUMNS(A1:C3)Result: 3.
Read a range written as text
INDIRECT turns the text reference into a range. Use a direct reference when the address is already known.
=COLUMNS(INDIRECT("A1:J10"))Result: 10.
Count the header range
This counts the three selected header positions, even if one heading is empty. COUNTA instead counts nonempty values.
=COLUMNS(A1:C1)Result: 3.
Return the last column with INDEX
COLUMNS supplies the final column index within B2:C3. INDEX returns East’s February value. Extend both references together when the table grows.
=INDEX(B2:C3,1,COLUMNS(B2:C3))Result: 160.
Count all cells in a rectangle
Three rows multiplied by three columns gives nine cells. This is the area of the range, not its populated-cell count.
=ROWS(A1:C3)*COLUMNS(A1:C3)Result: 9.
Choose the row by its label
MATCH finds East within A2:A3. COLUMNS supplies the last position within the numeric range. This avoids hardcoding the row position while preserving an explicit table boundary.
=INDEX(B2:C3,MATCH("East",A2:A3,0),COLUMNS(B2:C3))Result: 160.
Other Google Sheets articles you may also like