Sum in corresponding column, based on all matching acct numbers, and a positive match of another column to an array

diversification

New Member
Joined
Jun 24, 2020
Messages
37
Office Version
  1. 365
Platform
  1. 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
AccountActivity_DateTransaction_TypeAmountFormula 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 TypesSecond Types
RemovalSale
GiftPurchase
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,
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about
+Fluff 1.xlsm
ABCDEFGHIJK
1AccountActivity_DateTransaction_TypeAmountFormula NeededFirst TypesSecond Types
21111111115/03/2021Distribution-9000RemovalSale
31212121215/03/2021Capital300300GiftPurchase
41322314415/03/2021Capital100100Deletion
52222333315/03/2021Gift37.59100.24100.24Capital
62222333315/03/2021Gift12.53100.24100.24Bridge
72222444415/03/2021Gift2525
86545464615/03/2021Gift10001000
98888222215/03/2021Sale127.9146.38146.38
108888222215/03/2021Gift12.79146.38146.38
119999555515/03/2021Purchase3000
123333888812/03/2021Capital50100
134444222208/03/2021Deletion-11900
142222333305/03/2021Gift37.59100.24100.24
152222333305/03/2021Removal12.53100.24100.24
163333888805/03/2021Capital50100
178888222205/03/2021Gift121.45146.38146.38
188888222205/03/2021Bridge12.14146.38146.38
191717171703/03/2021Removal-66.1-535.55
201717171703/03/2021Removal-22.99-535.55
211717171703/03/2021Removal-30.9-535.55
221717171703/03/2021Sale-25.91-535.55
231717171703/03/2021Removal-39.32-535.55
241717171703/03/2021Removal-217.54-535.55
251717171703/03/2021Removal-83.46-535.55
261717171703/03/2021Gift-47.11-535.55
271717171703/03/2021Purchase-58.99-535.55
281717171703/03/2021Deletion-28.13-535.55
291112333301/03/2021Gift25002500
301322314401/03/2021Purchase100100
311984848801/03/2021Gift10001000
322222222201/03/2021Deposit39.660
334444222201/03/2021Gift11900
345555111101/03/2021Distribution-5568.870
355555444401/03/2021Distribution-1051.720
368888888801/03/2021Removal-1500-1500
379999999901/03/2021Sale-25000
Test
Cell Formulas
RangeFormula
E2:E37E2=SUM(SUMIFS(D:D,A:A,A2,C:C,$J$2:$J$6))
 
Upvote 0
If you want to use structured table references, then:

=SUM(SUMIFS([Amount],[Account],[@Account],[Transaction_Type],Table2[First Types]))
 
Upvote 0
Solution
Yep, this worked. Thank you both! The structured ref version is the one I went with, as that's how I'm writing the rest of the workbook.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top