Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for Subtotaling
I need to create a formula that will subtotal a $ amount
based on an ID#... I have tried the "subtotal feature built into excel, but it is far too distorting and disruptive to my spreadsheet. What I am wanting is a formula that will give a subtotal in a 3rd column... EXAMPLE: Column (A) is an ID#. Column (B) is a $ amount. Column (C) is my subtotal formula. This will be sorted by column (A). (A1)= 1 (A2)= 1 (A3)= 2 (B1)= 4.45 (B2) = 1.50 (B3)= $5.25 RESULT (C1)= 5.95 (C2) = 5.95 (C3)= $5.25 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for Subtotaling
Hi Jeremiahb, (anonymous poster via CDO)
Column C is to produce the total of Column B items for a specific ID in Column A. So C1 and C2 will show same total since they have same ID in Column A. I do not know how subtotal enters into your question at all. C1: =SUMIF(A:A,A1,B:B) C2: =SUMIF(A:A,A2,B:B) C3: =SUMF(A:A,A3,B:B) Perhaps if you want a subtotal you might prefer: C1: =IF(COUNTIF(A:A,A1)=COUNTIF(A$1:A1,A1),SUMIF(A:A,A 1,B:B),"") C2: =IF(COUNTIF(A:A,A2)=COUNTIF(A$1:A2,A2),SUMIF(A:A,A 2,B:B),"") C3: =IF(COUNTIF(A:A,A3)=COUNTIF(A$1:A3,A3),SUMIF(A:A,A 3,B:B),"") C4: =SUM(C$1:OFFSET(C4,-1,0)) John Walkenbach has a writeup that includes SUMIF SUMIF, Tip 74:Summing and Counting Using Multiple Criteria using SUMIF, COUNTIF, SUM that should solve most counting and summing problems. http://www.j-walk.com/ss/excel/tips/tip74.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jeremiahb" wrote in message ... I need to create a formula that will subtotal a $ amount based on an ID#... I have tried the "subtotal feature built into excel, but it is far too distorting and disruptive to my spreadsheet. What I am wanting is a formula that will give a subtotal in a 3rd column... EXAMPLE: Column (A) is an ID#. Column (B) is a $ amount. Column (C) is my subtotal formula. This will be sorted by column (A). (A1)= 1 (A2)= 1 (A3)= 2 (B1)= 4.45 (B2) = 1.50 (B3)= $5.25 RESULT (C1)= 5.95 (C2) = 5.95 (C3)= $5.25 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotaling | Excel Worksheet Functions | |||
Subtotaling | Excel Worksheet Functions | |||
Subtotaling | Excel Worksheet Functions | |||
subtotaling | Excel Discussion (Misc queries) | |||
subtotaling every third row? | Excel Worksheet Functions |