diversification
New Member
- Joined
- Jun 24, 2020
- Messages
- 37
- Office Version
- 365
- Platform
- Windows
I'm sure I just did a horrible job explaining what I'm trying to do, so I'll explain. I've got two tables:
Table 1
Table 2
In the "Formula Needed" Column of Table 1, I want to do the following:
For each row, sum all values from the "Amount" column, such that the following 2 conditions are met:
1. The Account value = the Account value in the given row --> [@Account] = [Account]
AND
2. The Transaction_Type matches one of the values in the First Types column of Table 2 --> COUNTIF(Table 2[First Types], [@Transaction_Type])>0
As you can see, I've manually run the calculation for 2 of the account numbers.
Requirements:
>> I'm want to use structured references to accomplish this, and I do not want to use VBA code, only formulas.
What I've tried:
All sorts of stuff. Primarily I've been trying SUM(FILTER type of stuff, with a COUNTIF imbedded. Also tried a SUMIF, and SUMIFS. Tried a few other things as well and cannot get it to work. I'd really appreciate some help!
Thank you,
Table 1
Account | Activity_Date | Transaction_Type | Amount | Formula Needed |
---|---|---|---|---|
11111111 | 3/15/2021 | Distribution | -900 | |
12121212 | 3/15/2021 | Capital | 300 | |
13223144 | 3/15/2021 | Capital | 100 | |
22223333 | 3/15/2021 | Gift | 37.59 | 100.24 |
22223333 | 3/15/2021 | Gift | 12.53 | 100.24 |
22224444 | 3/15/2021 | Gift | 25 | |
65454646 | 3/15/2021 | Gift | 1000 | |
88882222 | 3/15/2021 | Sale | 127.9 | 146.38 |
88882222 | 3/15/2021 | Gift | 12.79 | 146.38 |
99995555 | 3/15/2021 | Purchase | 300 | |
33338888 | 3/12/2021 | Capital | 50 | |
44442222 | 3/8/2021 | Deletion | -1190 | |
22223333 | 3/5/2021 | Gift | 37.59 | 100.24 |
22223333 | 3/5/2021 | Removal | 12.53 | 100.24 |
33338888 | 3/5/2021 | Capital | 50 | |
88882222 | 3/5/2021 | Gift | 121.45 | 146.38 |
88882222 | 3/5/2021 | Bridge | 12.14 | 146.38 |
17171717 | 3/3/2021 | Removal | -66.1 | |
17171717 | 3/3/2021 | Removal | -22.99 | |
17171717 | 3/3/2021 | Removal | -30.9 | |
17171717 | 3/3/2021 | Sale | -25.91 | |
17171717 | 3/3/2021 | Removal | -39.32 | |
17171717 | 3/3/2021 | Removal | -217.54 | |
17171717 | 3/3/2021 | Removal | -83.46 | |
17171717 | 3/3/2021 | Gift | -47.11 | |
17171717 | 3/3/2021 | Purchase | -58.99 | |
17171717 | 3/3/2021 | Deletion | -28.13 | |
11123333 | 3/1/2021 | Gift | 2500 | |
13223144 | 3/1/2021 | Purchase | 100 | |
19848488 | 3/1/2021 | Gift | 1000 | |
22222222 | 3/1/2021 | Deposit | 39.66 | |
44442222 | 3/1/2021 | Gift | 1190 | |
55551111 | 3/1/2021 | Distribution | -5568.87 | |
55554444 | 3/1/2021 | Distribution | -1051.72 | |
88888888 | 3/1/2021 | Removal | -1500 | |
99999999 | 3/1/2021 | Sale | -2500 |
Table 2
First Types | Second Types |
---|---|
Removal | Sale |
Gift | Purchase |
Deletion | |
Capital | |
Bridge |
In the "Formula Needed" Column of Table 1, I want to do the following:
For each row, sum all values from the "Amount" column, such that the following 2 conditions are met:
1. The Account value = the Account value in the given row --> [@Account] = [Account]
AND
2. The Transaction_Type matches one of the values in the First Types column of Table 2 --> COUNTIF(Table 2[First Types], [@Transaction_Type])>0
As you can see, I've manually run the calculation for 2 of the account numbers.
Requirements:
>> I'm want to use structured references to accomplish this, and I do not want to use VBA code, only formulas.
What I've tried:
All sorts of stuff. Primarily I've been trying SUM(FILTER type of stuff, with a COUNTIF imbedded. Also tried a SUMIF, and SUMIFS. Tried a few other things as well and cannot get it to work. I'd really appreciate some help!
Thank you,