Sumproduct at each change in voucher no

[nikhil]

I was having to manually put a subtotal formula for large amount of data. I have a column where I want result of sumproduct of two columns at each change on voucher no (another column). I just want to copy the formula for the whole column rather than manually calculating it (no of rows will keep on varying). I wished I could have posted a sample data to explain the problem better. I also have doubts whether sumproduct is the right way i am approaching the problem.

 

[Clarity]

In your sumproduct formula if your refer to the unique voucher number you will be able to use the same formula for each row. Have a look at this post from Chandoo:


http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

 

[VaraK]

Hi Nikhil,


From what I understood from your post, I think Subtotals would be one option you can look into.

 

[nikhil]

Dear Clarity,

No I am not referring to one unique voucher no. I have a column with many voucher numbers (rows) and I wanted a sumproduct of each voucher no. Is there any way I can post the excel data sample for easy understanding?

 

[Hui]

Assuming:


Voucher No A2:A100

Field1: B2:B100

Field2: C2:C100

Then in column ?, Row 4 put:

=IF(A5<>A4,Sumproduct(1*($A$2:$A$100=A4),($B$2:$B$100),($C$2:$C$100)),"-")

Copy up/down and adjust ranges to suit

 

[nikhil]

Date Particulars Vch No. Quantity Rate ucp total

08/04/2010 Bhandari SR-TN/1/10-11 -2 No. -Rs.2,670.00

9644YM08 -1 No. 1275.00/No.

2451YM02 -1 No. 1395.00/No.

12/04/2010 Ajay TN/0001/10-11 1 No. Rs.2,650.00

9714BM01 1 No. 2650.00/No.

12/04/2010 Ajay TN/0002/10-11 1 No. Rs.5,500.00

9223SM02 1 No. 5500.00/No.

24/04/2010 Novelty SR-TN/3/10-11 -4 No. -Rs.9,175.00

2111YM02 -1 No. 3250.00/No.

2111YM03 -1 No. 3250.00/No.

1319YM02 -1 No. 1450.00/No.

2401YM01 -1 No. 1225.00/No.


In the above illustration, I am trying to have a sumproduct result in UCP total column at each change in voucher no.

 

[nikhil]

Thanks Hui for the quick response. I could not understand, only if you would give me an illustration.


I have also tried to post the data (though not very successfully by text file).