View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier[_3_] Roger Govier[_3_] is offline
external usenet poster
 
Posts: 2,480
Default 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!