Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Putting subtotals in $ descending order
I have exported a report from a Donor based program to Excel v 2003. The
columns are donor name, date of donation and dollar amount of donation. Some donors have more than one donation. All donors have subtotals for their donations, the subtotal is below the detail of donation amounts. How can I put this report in order of donation subtotals, highest to lowest. This is an example of how an entry might look Column a Column b Column c row 5 John Smith 4/29/05 $5,000 row 6 John Smith 6/20/06 $10,000 row 7 2 $15,000 row 9 Jane Doe 4/04/07 50.00 row 10 Jane Doe 4/25/07 1,000.00 row 11 Jane Doe 6/30/07 20,000.00 row 12 3 21,050.00 row 14 Bill Brown 6/20/06 10.00 row 15 1 10.00 In putting the subtotal amounts in descending order, it will need to keep the detail with it. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Putting subtotals in $ descending order
Here's one way, based on your sample data...
In cell D5, enter this formula: =IF(A5=A4,D4,IF(OR(ISNUMBER(B5),LEN(B5)=0),D4,D4+1 )) In cell E5, enter this formula: =IF(ISNUMBER(B5),C5,"") In cell F5, enter this formula: =SUMIF($D$5:$D$16,D5,$E$5:$E$16) (replace every 16 in this formula with the number of the row after your final subtotal) Select D5:F5 and copy down through the row after your final subtotal (16 in your example). Select all the data + the extra row (A5:F16 in your example). From the Data menu, select Sort. Sort by column F in descending order, then by column D in ascending order. Hope this helps, Hutch "Ms. Domzalski" wrote: I have exported a report from a Donor based program to Excel v 2003. The columns are donor name, date of donation and dollar amount of donation. Some donors have more than one donation. All donors have subtotals for their donations, the subtotal is below the detail of donation amounts. How can I put this report in order of donation subtotals, highest to lowest. This is an example of how an entry might look Column a Column b Column c row 5 John Smith 4/29/05 $5,000 row 6 John Smith 6/20/06 $10,000 row 7 2 $15,000 row 9 Jane Doe 4/04/07 50.00 row 10 Jane Doe 4/25/07 1,000.00 row 11 Jane Doe 6/30/07 20,000.00 row 12 3 21,050.00 row 14 Bill Brown 6/20/06 10.00 row 15 1 10.00 In putting the subtotal amounts in descending order, it will need to keep the detail with it. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Top 10 in descending order | Excel Worksheet Functions | |||
DESCENDING ORDER -in Form | Excel Discussion (Misc queries) | |||
How do I match all columns into descending order | New Users to Excel | |||
Drop Down List is not in Descending Order | Excel Discussion (Misc queries) | |||
Date in descending order to come up automatically in a column? | Excel Worksheet Functions |