The COLUMNS function

The COLUMNS function is a lookup and reference function which returns a count of the columns within the selected range of cells.

This is the format of the COLUMNS function:

=COLUMNS(Selected Range)

Here is an example of the COLUMNS function:

=COLUMNS(A3:F20) = 6

COLUMNS function returning a count of the number of columns contained in the selected range

It is a very useful function when you need to evaluate a formula using a defining aspect such as number of weeks, and you want that value to increment automatically as you progress along your template.

An example of a functional implementation of this would be to calculate the average sell-through percentage by period (week, month, etc…) of a product line. You can use the COLUMNS function to automatically calculate the divisor based on where in the template the formula sits. For this example, let’s say that the sell-through data for the first week begins in cell G7, sales units in G5, and received inventory in G6.

=(G$5/G$6)/COLUMNS($G$7:G$7) = (10/100)/1

=(H$5/H$6)/COLUMNS($G$7:H$7) = (15/100)/2

etc…