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

Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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

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" wrote in message
...
Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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

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 schrieb:
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" wrote in message
...
Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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

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" wrote in message
...
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 schrieb:
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" wrote in message
...
Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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

Sandy,

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

Hope this helps...

Dominik.


Sandy schrieb:
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" wrote in message
...
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 schrieb:
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" wrote in message
...
Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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

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




"Sandy" wrote in message
...
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" wrote in message
...
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 schrieb:
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" wrote in message
...
Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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

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




"Sandy" wrote in message
...
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" wrote in message
...
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 schrieb:
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" wrote in message
...
Hi Sandy,

you could use SUMPRODUCT.

If your data is in A2:D7 (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 schrieb:
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