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

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

 

[Roger Govier]

Hi Rachel

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

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

 

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

 

[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))

 

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

 

[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

 

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

 

[Bob Phillips]

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