Want to do sum by Acct Subacct

[Sandy]

I have an excel sheet wehre I have the followign coloums e.g below. I
wanted to get some kind of furmula or function that would let me do a sum
by every unique Acct\Subacct Combo

Acct Subacct DebitAmount CreditAmount
100 800 1000
100 800 4000
100 800 9000
100 1250 1400
100 1250 1600
100 1250 3000


So basically my sum would be

Acct Sub DebitAmount CrediAmount
100 800 5000 9000
100 1250 3000 3000

As there are thousands of lines I cant manually do this. I would apprecaite
if someone cas assist

Thanks in advance
Sandy

 

[Dominik Petri]

Hi Sandy,

you could use SUMPRODUCT.

If your data is in A27 (headers are in row 1) and your sums start in
row 10, then use this formula in C10:

=SUMPRODUCT(--($A$2:$A$7=$A10), --($B$2:$B$10=$B10), --($C$2:$C$10)) to
calculate the Debit Amount.

Copy the formula to D10 and you have the credit amount.

HTH,
Dominik.

 

[Sandy]

Dominik

Thanks very much

What I want to do is calculate based on whenever there is a cahnge in the
unique account\subaccouot combination.So there might be for certain
Account\subaccounts 15 lines and for others just 4 lines etc.

As there are going to be thousands of lines I want to just put a formula and
roll ito down.

sp per e.g below

I would total for acct 100 sub 800 and then when the sub changes or the acct
changes then I would begin totalling again.

Thanks

S Commar

 

[Dominik Petri]

Hi Sandy,

I don't know if I understand you correctly, but I suppose your problem
is this:

- You have thousands of lines with data
- each line has (e.g. in columns A and B) the Acct and Subacct numbers
and (e.g. in columns C and D) debit and/or credt amounts
- You want to sum the debit and credit amounts for each Acct-Subacct
combination.


If this is correct, then first create a list of all unique Acct-Subacct
combinations. You could do this using Excel's Advanced Filter.

You'll then have these combinations e.g. in the range F1 to G500
(assuming you have 500 different combinations).

Assuming you have 10.000 rows with data (and the first row contains the
column headers), put this formula in H1:
=SUMPRODUCT(--($A$2:$A$10000=$F1), --($B$2:$B$10000=$G1), --(C$2:C$10000))

Now you can copy this formula from H1 to I1 and copy both formulas down
to row 500.

Is this what you want?

Regards,
Dominik.

 

[Sandy]

Dominik

Thanks
That's exactly what I want . Somehow I am not able to Advanced Filter. I get
an error when I try and copy the unique list to a new area on the grid. The
error I get references invalid field. Can you please guide me on this
process of Adavnced filtering.

.. THanks a lot

 

[Dominik Petri]

Sandy,

glad that the formula works for you. Concerning Excel's Advanced Filter,
you find a very good and in-depth explanation here:
http://www.contextures.com/xladvfilter01.html

Hope this helps...

Dominik.

 

[Sam Commar]

Dominik

Can I bother you with one more assistance item.

I had to add one more item in Coloum C for Project so now I have

Acct Subacct Project DebitAmt CreditAmt

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

I was trying to extrapolate the formula you had provided
H1: >
=SUMPRODUCT(--($A$2:$A$10000=$F1), --($B$2:$B$10000=$G1), --(C$2:C$10000))
to capture the one more variable in Coloum C (for Project ) but to no avail.

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 sum
the debit and credit amounts for each Acct-Subacct-Project combination.

Thank you so much for all your help. Its been a lifesaver.

Sandy

 

[Sam Commar]

Dominik

Can I bother you with one more assistance item.

I had to add one more item in Coloum C for Project so now I have

Acct Subacct Project DebitAmt CreditAmt

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

I was trying to extrapolate the formula you had provided
H1: >
=SUMPRODUCT(--($A$2:$A$10000=$F1), --($B$2:$B$10000=$G1), --(C$2:C$10000))
to capture the one more variable in Coloum C (for Project ) but to no avail.

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 sum
the debit and credit amounts for each Acct-Subacct-Project combination.

Thank you so much for all your help. Its been a lifesaver.

Sandy