How to I sum data from one sheet based off mulitple variables?
 

I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly appreciated! I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

Roger,

Thank you! I think I may have typed this question poorly...I couldn't get
your formula to work :( I just gave me zeros... I should have made tab 2
look like this:

Account (hard coded): 123465 101112
Trans (trans hard coded):
145 125.00 550.00
205 300.00 -
455 ( 75.00) (350.00)

Do you think the change in layout is why the formula didn't work? Sorry, I
should have typed it the right way the first time :) The dollars are where
the formulas should go...

Thanks again!

"Roger Govier" wrote:

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

=SUMPRODUCT((Sheet1!$A$2:$A$20=B2)*(Sheet1!$B$2:$B $20=A2)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

--
__________________________________
HTH

Bob

"Rachel S." wrote in message
...
Roger,

Thank you! I think I may have typed this question poorly...I couldn't get
your formula to work :( I just gave me zeros... I should have made tab 2
look like this:

Account (hard coded): 123465 101112
Trans (trans hard coded):
145 125.00 550.00
205 300.00 -
455 ( 75.00) (350.00)

Do you think the change in layout is why the formula didn't work? Sorry,
I
should have typed it the right way the first time :) The dollars are
where
the formulas should go...

Thanks again!

"Roger Govier" wrote:

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal
length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly
appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

Hi Rachel

Bob has quite rightly spotted that your credits are the same sign as your
debits, hence the need to subtract.
Given what you now say about your data, I am assuming that the first hard
coded Account is in B2, and the first hard coded Trans is in A3
If so then
=SUMPRODUCT((Sheet1!$A$2:$A$20=B$2)*(Sheet1!$B$2:$ B$20=$A3)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

If I have it wrong, change the references B2 and A3 to suit, but note that
the row is fixed in the first case ($2) and the column is fixed in the
second case ($A3)
Copy across and down to suit.
--
Regards
Roger Govier

"Rachel S." wrote in message
...
Roger,

Thank you! I think I may have typed this question poorly...I couldn't get
your formula to work :( I just gave me zeros... I should have made tab 2
look like this:

Account (hard coded): 123465 101112
Trans (trans hard coded):
145 125.00 550.00
205 300.00 -
455 ( 75.00) (350.00)

Do you think the change in layout is why the formula didn't work? Sorry,
I
should have typed it the right way the first time :) The dollars are
where
the formulas should go...

Thanks again!

"Roger Govier" wrote:

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal
length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly
appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

Thank you both for your help. Now I am getting a #VALUE! message...I am not
sure what I am doing wrong but I'll keep trying.

Thanks again for all your help! Have a great day!

Rachel

"Roger Govier" wrote:

Hi Rachel

Bob has quite rightly spotted that your credits are the same sign as your
debits, hence the need to subtract.
Given what you now say about your data, I am assuming that the first hard
coded Account is in B2, and the first hard coded Trans is in A3
If so then
=SUMPRODUCT((Sheet1!$A$2:$A$20=B$2)*(Sheet1!$B$2:$ B$20=$A3)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

If I have it wrong, change the references B2 and A3 to suit, but note that
the row is fixed in the first case ($2) and the column is fixed in the
second case ($A3)
Copy across and down to suit.
--
Regards
Roger Govier

"Rachel S." wrote in message
...
Roger,

Thank you! I think I may have typed this question poorly...I couldn't get
your formula to work :( I just gave me zeros... I should have made tab 2
look like this:

Account (hard coded): 123465 101112
Trans (trans hard coded):
145 125.00 550.00
205 300.00 -
455 ( 75.00) (350.00)

Do you think the change in layout is why the formula didn't work? Sorry,
I
should have typed it the right way the first time :) The dollars are
where
the formulas should go...

Thanks again!

"Roger Govier" wrote:

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal
length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly
appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

Just incase you are curious, this is what I am typing in...

=SUMPRODUCT((Sheet1!$A$2:$A$20=B$1)*(Sheet1!$B$2:$ B$20=$A3)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

Thanks again!

"Roger Govier" wrote:

Hi Rachel

Bob has quite rightly spotted that your credits are the same sign as your
debits, hence the need to subtract.
Given what you now say about your data, I am assuming that the first hard
coded Account is in B2, and the first hard coded Trans is in A3
If so then
=SUMPRODUCT((Sheet1!$A$2:$A$20=B$2)*(Sheet1!$B$2:$ B$20=$A3)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

If I have it wrong, change the references B2 and A3 to suit, but note that
the row is fixed in the first case ($2) and the column is fixed in the
second case ($A3)
Copy across and down to suit.
--
Regards
Roger Govier

"Rachel S." wrote in message
...
Roger,

Thank you! I think I may have typed this question poorly...I couldn't get
your formula to work :( I just gave me zeros... I should have made tab 2
look like this:

Account (hard coded): 123465 101112
Trans (trans hard coded):
145 125.00 550.00
205 300.00 -
455 ( 75.00) (350.00)

Do you think the change in layout is why the formula didn't work? Sorry,
I
should have typed it the right way the first time :) The dollars are
where
the formulas should go...

Thanks again!

"Roger Govier" wrote:

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal
length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to get
nested If/then or index/match to work. Any help is greatly
appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!

How to I sum data from one sheet based off mulitple variables?
 

It sounds like the perennial problem of having text in the number fields.

--
__________________________________
HTH

Bob

"Rachel S." wrote in message
...
Just incase you are curious, this is what I am typing in...

=SUMPRODUCT((Sheet1!$A$2:$A$20=B$1)*(Sheet1!$B$2:$ B$20=$A3)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

Thanks again!

"Roger Govier" wrote:

Hi Rachel

Bob has quite rightly spotted that your credits are the same sign as your
debits, hence the need to subtract.
Given what you now say about your data, I am assuming that the first hard
coded Account is in B2, and the first hard coded Trans is in A3
If so then
=SUMPRODUCT((Sheet1!$A$2:$A$20=B$2)*(Sheet1!$B$2:$ B$20=$A3)*(Sheet1!$C$2:$C$20-Sheet1!$D$2:$D$20))

If I have it wrong, change the references B2 and A3 to suit, but note
that
the row is fixed in the first case ($2) and the column is fixed in the
second case ($A3)
Copy across and down to suit.
--
Regards
Roger Govier

"Rachel S." wrote in message
...
Roger,

Thank you! I think I may have typed this question poorly...I couldn't
get
your formula to work :( I just gave me zeros... I should have made
tab 2
look like this:

Account (hard coded): 123465 101112
Trans (trans hard coded):
145 125.00 550.00
205 300.00 -
455 ( 75.00) (350.00)

Do you think the change in layout is why the formula didn't work?
Sorry,
I
should have typed it the right way the first time :) The dollars are
where
the formulas should go...

Thanks again!

"Roger Govier" wrote:

Hi Rachel

In C2 of Sheet2 enter
=SUMPRODUCT((Sheet1!$B$2:$B$1000=B2)*(Sheet1!$A$2: $A$1000=B2)*Sheet1$C2:D1000)
and copy down as required.

Extend each of the ranges to suit, but ensure they are all of equal
length.
--
Regards
Roger Govier

"Rachel S." <Rachel wrote in message
...
I need to sum data based off multiple variables and was not able to
get
nested If/then or index/match to work. Any help is greatly
appreciated!
I
have put a sample below:

Tab 1:
Account Trans Type Debit Credit
123456 145 125.00
123456 205 275.00
123456 205 25.00
123456 455 75.00
101112 455 200.00
101112 145 50.00
101112 455 150.00
101112 145 500.00

Tab 2 (calculations here):
Type Account Amount
145 123456 125.00
205 123456 300.00
455 123456 (75.00)
145 101112 550.00
455 101112 (350.00)

Tab 2 should give me the total amounts by trans type, by account. I
cannot
modify the data in tab 1.

Any advice is greatly appreciated!