The Microsoft Excel COLUMN &
COLUMNS functions are Lookup or Reference functions, which are useful for look
up and providing the column number of a given cell reference or range.
The Microsoft Excel COLUMN
function is the most popular member of the Reference functions family. This
function is useful for lookup & providing the column number of a specific
cell reference.
As well as COLUMN Function, the Microsoft
Excel COLUMNS function is another important reference function that is used
to receive an Excel range or reference of specific cell and returns the number
of columns that are contained within the range or specific cell.
Purpose:
To providing the column number of
a given cell reference or range.
Syntaxes of COLUMN & COLUMNS Functions:
The syntaxes of COLUMN &
COLUMNS Functions in Excel are as follows;
- =COLUMN([reference])
- =COLUMNS(array)
Return value:
The number of reference or range.
Arguments:
- Range - The range of cells of which we want to find out the number of columns.
- Reference – The cell or range of cells.
- Array - An array or array formula, or a reference to a range of cells for which we want to find out the numbers of columns.
You can download free
the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below
download icon,
This article explains in simple terms how to
use COLUMN & COLUMNS Functions. It takes a step-by-step approach, &
following the below contents, we will touch in this post.
- Microsoft Excel COLUMN Function explanation through various examples.
- Microsoft Excel COLUMNS Function explanation through various examples.
Let’s start to learn something
new about the COLUMN & COLUMNS Functions of Microsoft Excel.
1. Microsoft
Excel COLUMN Function - Example 1.
Microsoft Excel COLUMN Function is
used to look up and providing the column number of a given cell reference or
range, so now we explain COLUMN Function through various examples, let’s see
the below example,
Explanation: - In this
example we have taken the information, in which from "Row 2"
to "Row 11" which contains the information of Reference,
Function Used & the last "D" column contains a formula
that how we can use the COLUMN Function to get our specific return. As
we see that the cell "D6" contains the COLUMN Function & "B6
& C6” are our given information, so we have selected the cell "D6"
& " () " it means that we have not provided any reference but
actually, the selected cell has the reference of D6, so the Column
"D" number is "4" which is the exact answer.
In this scenario, we have used a
formula like below,
=COLUMN()
|
Example 2.
If in the case when we use the combo
of three functions like SUM, INDEX & COLUMN, so then see the magic of
COLUMN Function in the below example,
Explanation: - In this
example we see that the "B3:B10" contains the handing of
column "C3:C10" & "D3:D10" information or
in other words Column 1 & Column 2 is information provided
& the combo function is used in "F3:M3" range, through
this combo functions we want to sum the Column 1 & Column
2 respectively in the range of "F3:M3", As by using the
combo function in selected cell "H3", we find out that this
function sum the 3+3 = 6, which is the exact answer.
In this scenario, we have used a
formula like below,
=SUM(INDEX($C$3:$D$10,COLUMN(H3)-COLUMN($E3),0))
|
2. Microsoft Excel COLUMNS Function - Example 1.
Microsoft Excel COLUMNS Function is used to return
the number of columns that are contained within the range, so now we explain COLUMNS
Function through various examples, so see the below example,
Explanation: - In this
example we see that from "Row 2" to "Row 11"
which contains the information of Reference, Function Used & the
last "D" column contains a formula that how we can use the
COLUMNS Function to get our specific return. As we see that the cell "D8"
contains the COLUMNS Function & "B8 & C8” are our given
information, so we have selected the cell "D8" & "(1:1)"
it means that the whole ROW # 8 is selected, so I am using Microsoft
Office 2013 Version & this version have total 16,384/ Cells in Row #
8, if you use this function in other office versions, maybe the
result very with this one.
In this scenario, we have used a
formula like below,
=COLUMNS(1:1)
|
Example 2.
If in the case when we want to check
the number of columns in a specific range, so then see the below example,
Explanation: - In this
example we see that from "Row 2" to "Row 11"
which contains the information of Reference, Function Used & the
last "D" column contains formula that how we can use the
COLUMNS Function to get our specific return. As we see that the cell "D7"
contains the COLUMNS Function & "B7 & C7” are our given
information, so we have selected the cell range of "B4:H11",
as the result is 7, actually if we count alphabet from "B to H",
the result will be 7 number of Columns.
In this scenario, we have used a
formula like below,
=COLUMNS(B4:H11)
|
No comments:
Post a Comment