## Count per row

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.

The first example has this data table.

I want to count the value "C" per row. In cell I1, type: C.

In cell I2, type:

Copy cell I2 and paste to I3:I10.

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 I2. If you change the value in cell I1 to "B", the formulas in I2:I10 recalculates and return new values.

Let us sort the table. Select cell range A1:I10. Go to tab "Home" on the ribbon. Click "Sort & Filter" button. Click "Filter".

Drop down list arrows appear next to each header, see picture above. Click the arrow next to "C".

Click "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

### Dynamic counting

Now on to a more interesting and complicated example. The formula here returns names sorted in column D based on the number of "C"s per row.

Change the value in cell B3 and the list in cell range D3:E11 is instantly changed.

I have applied conditional formatting to cell range A14:H22 so you can easily verify the calculated numbers in E3:E11.

**Array formula in cell D3:**

**Array formula in cell E3:**

Functions in this formula: INDEX, MATCH, LARGE, MMULT, ROW COUNTIF, IF

Interested in learning more about excel formulas? Check out the Advanced excel course.

### Download excel *.xlsx file

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

### 3 Responses to “Count per row”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] 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: […]