Formula for Subtotaling

  • Thread starter Thread starter Jeremiahb
  • Start date Start date
J

Jeremiahb

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
 
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,A1,B:B),"")
C2: =IF(COUNTIF(A:A,A2)=COUNTIF(A$1:A2,A2),SUMIF(A:A,A2,B:B),"")
C3: =IF(COUNTIF(A:A,A3)=COUNTIF(A$1:A3,A3),SUMIF(A:A,A3,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
 
Back
Top