Sort based on frequency row-wise
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward.
The second example is a bit more complicated but dynamic and automatic, you only need to provide a search string, everything else is calculated by array formulas.
What's on this webpage
1. Count value per row
The first example has this data table, see image above. It is an Excel Table, select any cell in the data set and press CTRL + T to convert the data to an Excel Table.
I want to count the value "C" per row.
In cell J3, type:
Copy cell I2 and paste to I3:I10. You don't need to do this if you use an Excel Table as I did, see the image above. The Excel Table copies the formulas to cells below automatically.
The COUNTIF function counts the number of cells that meet a given condition.
There are 4 C's in row 2 so the formula returns 4 in cell J3. If you change the value in cell J2 to "B", the formulas in J3:J11 recalculates and return new values.
Let us sort the Excel table.
Drop-down list arrows appear next to each header, see picture above. Press with left mouse button on the arrow next to "C".
Press with left mouse button on "Largest to smallest". The data table shows the count of "C" per row, sorted largest to smallest.
Learn more about the COUNTIF function.
Do you know why $I$1 in COUNTIF(B2:H2,$I$1) has dollar signs?
Read this post: Absolute and relative cell references
2. Dynamic counting per row sorted from large to small
Now on to a more interesting and complicated example. The formula here returns names sorted in column E based on the number of "C"s per row.
How is this possible? The MMULT function is able to count cells per row based on a condition, it returns an array that corresponds to the number of rows. Change the value in cell C3 and the list in cell range E3:F11 is instantly changed.
I have applied conditional formatting to cell range B14:I22 so you can easily verify the calculated numbers in E3:E11.
Array formula in cell E3:
Excel 365 dynamic array formula in cell E3:
The formula above works only in Excel 365, it contains a new function: SORTBY.
Array formula in cell E3:
Explaining array formula in cell E3
Step 1 - Find search value in cell range
The equal sign compares the value in cell C3 to values in cell range c14:I22.
$C$14:$I$22=$C$3
becomes
{"C", "C", "B", "C", "C", "A", "B";"B", "B", "B", "C", "C", "C", "A";"C", "B", "B", "A", "A", "A", "A";"A", "B", "B", "C", "C", "C", "C";"A", "B", "C", "C", "C", "C", "B";"C", "B", "B", "A", "A", "C", "B";"A", "A", "C", "C", "C", "C", "B";"B", "A", "A", "A", "A", "B", "B";"B", "B", "A", "B", "A", "C", "B"}="C"
and returns
{TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}
Step 2 - Convert boolean values to numerical values
The parentheses lets you control the order of calculation, we want to compare $B$14:$H$22 with $B$3 before we multiply with 1.
($B$14:$H$22=$B$3)*1
becomes
{TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}*1
and returns
{1, 1, 0, 1, 1, 0, 0;0, 0, 0, 1, 1, 1, 0;1, 0, 0, 0, 0, 0, 0;0, 0, 0, 1, 1, 1, 1;0, 0, 1, 1, 1, 1, 0;1, 0, 0, 0, 0, 1, 0;0, 0, 1, 1, 1, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0}.
Step 3 - Create an array containing corresponding column numbers
The COLUMN function returns the column number from a cell reference.
COLUMN($B$14:$H$22)
returns {2, 3, 4, 5, 6, 7, 8}.
Step 4 - Change numbers in array to 1
COLUMN($B$14:$H$22)^0
becomes
{2, 3, 4, 5, 6, 7, 8}^0
and returns {1, 1, 1, 1, 1, 1, 1}.
Step 5 - Evaluate MMULT function
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.
MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0))
becomes
MMULT({1, 1, 0, 1, 1, 0, 0;0, 0, 0, 1, 1, 1, 0;1, 0, 0, 0, 0, 0, 0;0, 0, 0, 1, 1, 1, 1;0, 0, 1, 1, 1, 1, 0;1, 0, 0, 0, 0, 1, 0;0, 0, 1, 1, 1, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0}*1, {1, 1, 1, 1, 1, 1, 1})
and returns
{4; 3; 1; 4; 4; 2; 4; 0; 1}
Step 6 - Extract kth largest number
The LARGE function returns the kth largest number from a cell range or array.
LARGE(array, k)
LARGE(MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ROWS($B$1:B1))
becomes
LARGE({4; 3; 1; 4; 4; 2; 4; 0; 1}, ROWS($B$1:B1))
The ROWS function returns a number representing the number of rows in a cell reference.
Cell reference $B$1:B1 grows when the cell is copied and pasted to cells below. This makes the ROWS function return a new number in each cell.
LARGE({4; 3; 1; 4; 4; 2; 4; 0; 1},1)
and returns 4.
Step 7 - Count values above the current cell
The COUNTIF function counts cells that equal a given condition. The cell reference $E$2:$E2 grows when the cell is copied and pasted to cells below, this lets the formula check all previous values.
COUNTIF($E$2:$E2, $B$14:$B$22)=0
becomes
{0;0;0;0;0;0;0;0;0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 8 - Filter count numbers based on previous values above
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(COUNTIF($E$2:$E2, $B$14:$B$22)=0, MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), "")
becomes
IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {4; 3; 1; 4; 4; 2; 4; 0; 1}, "")
and returns {4; 3; 1; 4; 4; 2; 4; 0; 1}.
Step 9 - Find the relative position of the kth largest number in the array
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(LARGE(MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ROWS($B$1:B1)), IF(COUNTIF($E$2:$E2, $B$14:$B$22)=0, MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ""), 0)
becomes
MATCH(4, {4; 3; 1; 4; 4; 2; 4; 0; 1}, 0)
and returns 1.
Step 10 - Get value
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX($B$14:$B$22, MATCH(LARGE(MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ROWS($B$1:B1)), IF(COUNTIF($E$2:$E2, $B$14:$B$22)=0, MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ""), 0))
becomes
INDEX($B$14:$B$22, 1)
and returns "Wilhelm" in cell D3.
Check out: Dynamic scoreboard
Interested in learning more about excel formulas? Check out the Advanced excel course.
Count values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
3 Responses to “Sort based on frequency row-wise”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] Take a look at the full article here: Count Per Row […]
Hi Oscar,
I have a problem and i cant figure it out, even if i'm seraching for 2 days.
I have the following situation:
A1, B1, C1, D1, E1, F1
where
A1 = nick
b1 = 10
c1 = zack
d1 = 15
e1 - john
f1 = 13
what formula should i use to get them ordered counting the numbers but names still being asociated, like this:
a1 = zack
b1 = 15
c1 = john
d1 = 13
e1 = nick
f1 = 10
hope i was clear...
Thank you
Have a nice day
[…] Denisa asks: […]