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:

1. Select range D3:J16
2. Click "Conditional Formatting" button
3. Click "New Rule..."
4. Click "Use a formula to determine which cells to format"
5. Copy and paste this conditional formatting formula: =$D3="TOTAL:"
6. Click "Format" button
7. Click "Border" tab
8. Create line
9. 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.

Related posts:

1. Sort values in parallel (array formula)
2. Count matching cell values in two columns in excel
3. Extract cell values in a range using a criterion in excel