Want to do a SumProduct formula

[Sam Commar]

I have the following coloums in my Excel sheet. I have close to 30000
records and this will be an ongoing monthly process that I will need to do.

My data has 5 coloums from A to E

The 5 coloums of data are as below
A B C D E
Acct Subacct Project DebitAmt CreditAmt



I was trying to use a SumProduct formula to do do the Totals of the DebitAmt
and the CreditAmt by each distinct
Acct Subacct Project combination.


I have used the Advanced filter so that now in Coloum F G H have the
distinct Acct Subacct Project combinations.


Can I bother you to please guide me that once I have the distinct
Acct Subacct Project combinations in Coloum F G H- how can I get the sum of
the debitamt and creditamt amounts for each Acct-Subacct-Project
combination.



Sandy

 

[Bernard Liengme]

If this was my task I would use a Pivot Table.

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes

 

[Harald Staff]

Hi Sansy

I insist that you try doing this with a Pivot table. Far faster, easier and
more reliable. See
http://www.cpearson.com/excel/pivots.htm

HTH. Best wishes Harald