SUBTOTAL QUERY

F

Finance Guru

Hi All,

Can some help. I am using Excel 2007

I have 3 columns

A B C
Invoice Amount Sub Total
D1000 50.00
D1000 1250.00
D1200 5000.00
D1200 1250.00
D1300 750.00
D1300 999.90

My question is this - how can I using the SUBTOTAL function in Excel,get it
to subtotal the total value for each item in Col A and put the subtotal of
these occurences in Column C. eg. in Column C for D1000 = 1300.00, D1200 =
6250.00 etc.

I have tried every way but I can only get excel to put the subtotal amount
in Col B after the end of each occurence of the totalled invoice numbers.Can
anyone assist?

Many thanks
FinanceGuru
 
P

Pete_UK

Try this in C2:

=IF(C2=C3,"",SUMIF(A:A,A2,B:B))

then copy it down. It should give you subtotals against the last entry
of consecutive values in A. This assumes that the data is sorted on
column A.

Data | Subtotals works in the way you describe, so you can't get the
effect you want using that.

Hope this helps.

Pete
 
F

Finance Guru

Hi Pete,

Thanks for your response. Yes I could do it that way,my question was more
whether I was missing anything in the SUBTOTAL ( not shouting ) function to
achieve this automatically.

Thanks again
FinanceGuru
 
P

Pete_UK

Well, putting a formula in one cell and copying it down is fairly
automatic, and will certainly be faster than the Data | Subtotal route
(which doesn't give you what you want anyway), if you have a lot of
invoices !

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top