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

R

Rachel S.

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!
 
R

Roger Govier

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.
 
R

Rachel S.

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!
 
B

Bob Phillips

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

Roger Govier

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.
 
R

Rachel S.

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
 
R

Rachel S.

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top