SUBTOTAL QUERY
 

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



--
Wales - the land of fire breathing dragons and rugby playing wizards.

SUBTOTAL QUERY
 

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

On Jul 31, 3:37*pm, Finance Guru
wrote:
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

--
Wales - the land of fire breathing dragons and rugby playing wizards.

SUBTOTAL QUERY
 

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
--
Wales - Where once miners toiled,now fire breathing dragons and rugby
playing wizards play in lush green valleys.


"Pete_UK" wrote:

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

On Jul 31, 3:37 pm, Finance Guru
wrote:
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

--
Wales - the land of fire breathing dragons and rugby playing wizards.

SUBTOTAL QUERY
 

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

On Jul 31, 4:27*pm, Finance Guru
wrote:
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
--
Wales - Where once miners toiled,now fire breathing dragons and rugby
playing wizards play in lush green valleys.



"Pete_UK" wrote:
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

On Jul 31, 3:37 pm, Finance Guru
wrote:
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

--
Wales - the land of fire breathing dragons and rugby playing wizards.- Hide quoted text -

- Show quoted text -

SUBTOTAL QUERY
 

I agree. Thanks again for your help
--
Wales - Can it really be the land of fire breathing dragons and rugby
playing wizards. Come on down and see for oneself.


"Pete_UK" wrote:

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

On Jul 31, 4:27 pm, Finance Guru
wrote:
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
--
Wales - Where once miners toiled,now fire breathing dragons and rugby
playing wizards play in lush green valleys.



"Pete_UK" wrote:
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

On Jul 31, 3:37 pm, Finance Guru
wrote:
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

--
Wales - the land of fire breathing dragons and rugby playing wizards.- Hide quoted text -

- Show quoted text -

SUBTOTAL QUERY
 

You're welcome.

Pete

On Jul 31, 5:04*pm, Finance Guru
wrote:
I agree. Thanks again for your help
--
Wales - Can it really be the land of fire breathing dragons and rugby
playing wizards. Come on down and see for oneself.