# COLUMNS Function in Excel

## Part 1: Introduce the COLUMNS Function in Microsoft Excel

π‘ Definition: The COLUMNS function in Microsoft Excel is a Lookup/Reference function.

π‘ Purpose: It counts the number of columns in an array or reference.

π‘ Syntax & Arguments:

syntax
`=COLUMNS(array) `

π‘ Explanation of Arguments: The ‘array’ is a necessary argument representing the array or reference for which you want to count the number of columns.

π‘ Return value: The COLUMNS function returns the number of columns in an array or reference.

π‘ Remarks: Remember that the COLUMNS function counts all columns, not just the non-blank columns.

## Part 2: Examples

### π Example 1

π― Purpose of Example: To find the number of columns in a given range.

Data Tables and Formulas:

ABCDE (Formula)
1NameAgeCityJobFormula
2John32NYCChef`=COLUMNS(A2:D2)`
3Anna25LANurse
4Mary28SFDoctor

Result Table:

ABCDE (Result)
1NameAgeCityJobResult
2John32NYCChef4
3Anna25LANurse
4Mary28SFDoctor

In this example, we aim to count the number of columns in a given range. By applying the COLUMNS function to the range A2:D2, we get the number 4 as a result, as there are 4 columns in this range.

### π Example 2

π― Purpose of Example: To understand how the COLUMNS function updates when copying formulas across columns.

Data Tables and Formulas:

ABCDE (Formula)
1NameAgeCityJobFormula
2John32NYCChef`=COLUMNS(\$A2:B2)`
3Anna25LANurse
4Mary28SFDoctor

Result Table:

ABCDE (Result)
1NameAgeCityJobResult
2John32NYCChef2
3Anna25LANurse
4Mary28SFDoctor

In this example, the formula counts the number of columns between columns A and B inclusive, which are 2 columns.

### π Example 3

π― Purpose of Example: To create a running count of columns using the COLUMNS function.

Data Tables and Formulas:

ABCDE (Formula)
1NameAgeCityJobFormula
2John32NYCChef`=COLUMNS(\$A2:A2)`
3Anna25LANurse
4Mary28SFDoctor

Result Table:

ABCDE (Result)
1NameAgeCityJobResult
2John32NYCChef1
3Anna25LANurse
4Mary28SFDoctor

In this example, the formula returns 1, as only 1 column (column A) is in the defined range. If you copy this formula across the row, the content will adjust, and the result will increment.

### π Example 4: COLUMNS with IF

π― Purpose of Example: To showcase how the IF function can be used with the COLUMNS function to decide based on the number of columns.

Data Tables and Formulas:

ABCDE (Formula)
1NameAgeCityJobFormula
2John32NYCChef`=IF(COLUMNS(A2:D2)>3, "Yes", "No")`
3Anna25LANurse
4Mary28SFDoctor

Result Table:

ABCDE (Result)
1NameAgeCityJobResult
2John32NYCChefYes
3Anna25LANurse
4Mary28SFDoctor

This example checks if the number of columns in a range exceeds 3. If it is, it returns “Yes”. If not, it replaces “No”.

### π Example 5: COLUMNS with SUM

π― Purpose of Example: To show how the SUM function can be used with the COLUMNS function to find the sum of values in columns.

Data Tables and Formulas:

ABCDE (Formula)
1Value 1Value 2Value 3Value 4Formula
21234`=SUM(A2:D2)/COLUMNS(A2:D2)`
32345
43456

Result Table:

ABCDE (Result)
1Value 1Value 2Value 3Value 4Result
212342.5
32345
43456

This example uses SUM to add the values in the columns and then divides them by the number of columns to get the average value.

### π Example 6: COLUMNS with VLOOKUP

π― Purpose of Example: To demonstrate how VLOOKUP can be used with the COLUMNS function to look up values based on column numbers.

Data Tables and Formulas:

ABCDE (Formula)
1NameAgeCityJobFormula
2John32NYCChef`=VLOOKUP("John", A2:D2, COLUMNS(A2:B2), FALSE)`
3Anna25LANurse
4Mary28SFDoctor

Result Table:

ABCDE (Result)
1NameAgeCityJobResult
2John32NYCChef32
3Anna25LANurse
4Mary28SFDoctor

In this example, the VLOOKUP function looks up the name “John” in the array A2:D2. The COLUMNS function is used to set the column index number. It returns the value in the second column of the array, which corresponds to the age of “John”.

### π Example 7: COLUMNS with COUNT

π― Purpose of Example: To show how the COUNT function can be used with the COLUMNS function to count numerical values across columns.

Data Tables and Formulas:

ABCDE (Formula)
1Value 1Value 2Value 3Value 4Formula
21234`=COUNT(A2:D2)/COLUMNS(A2:D2)`
32345
43456

Result Table:

ABCDE (Result)
1Value 1Value 2Value 3Value 4Result
212341
32345
43456

In this example, the COUNT function is used to count the number of numerical values in the columns, and then it is divided by the number of columns.

### π Example 8: COLUMNS with AVERAGE

π― Purpose of Example: To demonstrate how the AVERAGE function can be used with the COLUMNS function to find the average values across columns.

Data Tables and Formulas:

ABCDE (Formula)
1Value 1Value 2Value 3Value 4Formula
21234`=AVERAGE(A2:D2)*COLUMNS(A2:D2)`
32345
43456

Result Table:

ABCDE (Result)
1Value 1Value 2Value 3Value 4Result
2123410
32345
43456

This example uses AVERAGE to find the average values in the columns and then multiply them by the number of columns.

### π Example 9: COLUMNS with MAX

π― Purpose of Example: To illustrate how the MAX function can be used with the COLUMNS function to find the maximum value across columns.

Data Tables and Formulas:

ABCDE (Formula)
1Value 1Value 2Value 3Value 4Formula
21234`=MAX(A2:D2)*COLUMNS(A2:D2)`
32345
43456

Result Table:

ABCDE (Result)
1Value 1Value 2Value 3Value 4Result
2123416
32345
43456

In this example, the MAX function is used to find the maximum value in the columns, and then it is multiplied by the number of columns.

### π Example 10: COLUMNS with MIN

π― Purpose of Example: To show how the MIN function can be used with the COLUMNS function to find the minimum value across columns.

Data Tables and Formulas:

ABCDE (Formula)
1Value 1Value 2Value 3Value 4Formula
21234`=MIN(A2:D2)*COLUMNS(A2:D2)`
32345
43456

Result Table:

ABCDE (Result)
1Value 1Value 2Value 3Value 4Result
212344
32345
43456

In this example, the MIN function is used to find the minimum value in the columns, which is multiplied by the number of columns.

## Part 3: Tips and Tricks

1. π Use the COLUMNS function to create dynamic ranges in Excel.
2. π Remember that the COLUMNS function can also be used with data tables.
3. π Use COLUMNS with functions like INDEX and VLOOKUP for advanced data manipulation.