## Sort cell values into categories, part 2

*Article updated on April 05, 2009*

In this article I am going to enhance a previous article "Sort cell values into categories".

Here is a picture from the final result from the previous article.

Here is what I will do in this article. All amount columns sums automatically. A line separates the values from the summed values. The summed values and the line adjusts automatically to the column with most records.

Compare the picture below with the picture above and you will understand what I am talking about.

The formula in F3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1, 1, "")), INDEX($C$1:$C$15, SMALL(IF((E3=$A$2:$A$15)*(E$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), IF(D3="TOTAL:", SUM(INDIRECT("$F$3:$F$"&SMALL(ROW(), 1)-1)), "")) + Ctrl + Shift + Enter. Copy this formula down to F16.

The formula in G3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=G$1, 1, "")), INDEX($C$1:$C$15, SMALL(IF((G3=$A$2:$A$15)*(G$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), IF(D3="TOTAL:", SUM(INDIRECT("$H$3:$H$"&SMALL(ROW(), 1)-1)), "")) + Ctrl + Shift + Enter. Copy this formula down to G16.

The formula in H3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=G$1, 1, "")), INDEX($C$1:$C$15, SMALL(IF((G3=$A$2:$A$15)*(G$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), IF(D3="TOTAL:", SUM(INDIRECT("$H$3:$H$"&SMALL(ROW(), 1)-1)), "")) + Ctrl + Shift + Enter. Copy this formula down to H16.

To create the summation line in Excel 2007:

- Select range D3:J16
- Click "Conditional Formatting" button
- Click "New Rule..."
- Click "Use a formula to determine which cells to format"
- Copy and paste this conditional formatting formula: =$D3="TOTAL:"
- Click "Format" button
- Click "Border" tab
- Create line
- Click ok and ok

Download excel sample file for this article.

sort-cells-values-into-categories_total

(Excel 97-2003 Workbook *.xls)

Press F9 to generate new random dates and values and see the conditional formatting in action.

**Functions in this article:**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDIRECT(**ref_text,[a1]**)**

Returns the reference specified by a text string

**ROW(**reference**)** returns the rownumber of a reference

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 2 Responses to “Sort cell values into categories, part 2”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**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

Could be possible to modify this formula to text values for 'dates' or 'Amount'?

[…] functions. Sources already used: Can I remove blanks from a range without using sort? (FORUM) Sort cell values into categories, part 2 | Get Digital Help - Microsoft Excel resource (ATTACHED FILE, GREAT FORMULA) https://forum.openoffice.org/en/foru...ic.php?t=38056 Sorting Text […]