Scan for match and add to total.

RichKat

New Member
Joined
Nov 8, 2009
Messages
39
Office Version
  1. 365
Platform
  1. Windows
We are assigning accounts and departments to expenses on a monthly credit card statement. Each Row/Record has a debit column (G) and a Credit column (H) and the row also has an Account# Column (K) and a Department column (L). This looks something like this:

User​
Transaction Date​
DescriptionDebitCreditCategoryAccountDepartment
Brian​
8/18/2023​
MOUNTAINS EDGE
26​
Repairs/Maintenance66200100
Gabe​
8/19/2023​
RBT MARATHON PETRO1014
1​
200
Gabe​
8/19/2023​
RBT CIRCLE K # 23710
0.32​
200
Gabe​
8/22/2023​
CIRCLE K # 23706
50.02​
200
Gabe​
8/22/2023​
WALMART.COM
39.47​
200


Then I have a separate table that looks like this:

Department 100​
Department 200​
Account
Amount​
Account
Amount​
50001-10050001-200
65000-10065000-200
65500-10065500-200
65600-10065600-200
66200-10066200-200
66425-10066425-200
67150-10067150-200
67400-10067400-200
67575-10067575-200
Statement Total
$ -​


In this table I want to scan down the account and department columns and when the account and department match one of the cells in my table, I want to subtract H from G and add it to the total in that amount cell.

So for the first line in the first table, G-0 is 26 and it would be placed in the total next to the fifth cell from the top’s amount.

Any help would be much appreciated.

Thank you
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try this:

Mr excel questions 63.xlsm
ABCDEFGHIJKL
1
2UserTransaction DateDescriptionDebitCreditCategoryAccountDepartment
3Brian2023-08-18MOUNTAINS EDGE260Repairs/Maintenance66200100
4Gabe2023-08-19RBT MARATHON PETRO101401200
5Gabe2023-08-19RBT CIRCLE K # 2371000.32200
6Gabe2023-08-22CIRCLE K # 2370650.020200
7Gabe2023-08-22WALMART.COM39.470200
8
9
10
11
12Department 100Department 200
13AccountAmountAccountAmount
1450001-100#N/A50001-200
1565000-100#N/A65000-200
1665500-100#N/A65500-200
1765600-100#N/A65600-200
1866200-1002666200-200
1966425-100#N/A66425-200
2067150-100#N/A67150-200
2167400-100#N/A67400-200
2267575-100#N/A67575-200
23
24Statement Total26
25
RichKat
Cell Formulas
RangeFormula
B14:B22B14=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0))
B24B24=AGGREGATE(9,6,B14:B22)
 
Upvote 0
I just noticed in your footer that you use 2007. The formulas I have above will not work.
I think the section to the right below will work for you.
(But, it may be a good idea to put your excel version in your profile so it shows up on your button where forum members can more easily see it.)

Mr excel questions 63.xlsm
ABCDEFGHIJKL
1
2UserTransaction DateDescriptionDebitCreditCategoryAccountDepartment
3Brian2023-08-18MOUNTAINS EDGE260Repairs/Maintenance66200100
4Gabe2023-08-19RBT MARATHON PETRO101401200
5Gabe2023-08-19RBT CIRCLE K # 2371000.32200
6Gabe2023-08-22CIRCLE K # 2370650.020200
7Gabe2023-08-22WALMART.COM39.470200
8
9
10
11
12Department 100Department 200Department 100Department 200
13AccountAmountAccountAmountAccountAmountAccountAmount
1450001-100#N/A50001-20050001-100050001-200
1565000-100#N/A65000-20065000-100065000-200
1665500-100#N/A65500-20065500-100065500-200
1765600-100#N/A65600-20065600-100065600-200
1866200-1002666200-20066200-1002666200-200
1966425-100#N/A66425-20066425-100066425-200
2067150-100#N/A67150-20067150-100067150-200
2167400-100#N/A67400-20067400-100067400-200
2267575-100#N/A67575-20067575-100067575-200
23
24Statement Total26Statement Total26
RichKat
Cell Formulas
RangeFormula
B14:B22B14=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0))
H14:H22H14=IF(ISNA( INDEX($G$3:$G$7,MATCH(LEFT(G14,FIND("-",G14)-1)&SUBSTITUTE(G14,LEFT(G14,FIND("-",G14)),""),($K$3:$K$7)&($L$3:$L$7),0))),0, INDEX($G$3:$G$7,MATCH(LEFT(G14,FIND("-",G14)-1)&SUBSTITUTE(G14,LEFT(G14,FIND("-",G14)),""),($K$3:$K$7)&($L$3:$L$7),0)))
B24B24=AGGREGATE(9,6,B14:B22)
H24H24=SUM(H14:H22)
 
Upvote 0
Try this:

Mr excel questions 63.xlsm
ABCDEFGHIJKL
1
2UserTransaction DateDescriptionDebitCreditCategoryAccountDepartment
3Brian2023-08-18MOUNTAINS EDGE260Repairs/Maintenance66200100
4Gabe2023-08-19RBT MARATHON PETRO101401200
5Gabe2023-08-19RBT CIRCLE K # 2371000.32200
6Gabe2023-08-22CIRCLE K # 2370650.020200
7Gabe2023-08-22WALMART.COM39.470200
8
9
10
11
12Department 100Department 200
13AccountAmountAccountAmount
1450001-100#N/A50001-200
1565000-100#N/A65000-200
1665500-100#N/A65500-200
1765600-100#N/A65600-200
1866200-1002666200-200
1966425-100#N/A66425-200
2067150-100#N/A67150-200
2167400-100#N/A67400-200
2267575-100#N/A67575-200
23
24Statement Total26
25
RichKat
Cell Formulas
RangeFormula
B14:B22B14=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0))
B24B24=AGGREGATE(9,6,B14:B22)
Hey! Thanks so much. I'm sorry, I haven't updated my profile for a long while! I'm on 365 so that worked fine with slight adjustments for cell addresses. The exception is that the formula results in #N/A if all of the data isn't there. I would like for it to result in 0 if that's the case but I don't know how to test for #N/A. I tried "=IF(INDEX($G$2:$G$150,MATCH(TEXTBEFORE(Q3,"-")&TEXTAFTER(Q3,"-"),($K$2:$K$150)&($L$2:$L$150),0))="#N/A", 0, INDEX($G$2:$G$150,MATCH(TEXTBEFORE(Q3,"-")&TEXTAFTER(Q3,"-"),($K$2:$K$150)&($L$2:$L$150),0)))" but that still resulted in the #N/A.
 
Upvote 0
wrap it with IFERROR

Excel Formula:
=IFERROR(=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0)),0)
 
Upvote 0
wrap it with IFERROR

Excel Formula:
=IFERROR(=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0)),0)
Oh, great! Wow, so simple. I'm learning a LOT! It seems like that's the only way I learn is to beat my head against a problem, ask for help and then compare what works with what didn't. :) Thanks so much!
 
Upvote 0
You're welcome. I am happy you found a solution.

Best Wishes!
 
Upvote 0
You're welcome. I am happy you found a solution.

Best Wishes!
The sample that I first sent you leaves out one important thing... The items in the K and L columns may have duplicates and I need any row that meets the criteria to have the debit column subtracted from the credit column, from that row and THEN added to the totals in the small table. Does that make sense?
 
Upvote 0
The sample that I first sent you leaves out one important thing... The items in the K and L columns may have duplicates and I need any row that meets the criteria to have the debit column subtracted from the credit column, from that row and THEN added to the totals in the small table. Does that make sense?
can you post some examples and what are the expected results.
 
Upvote 0
ABEGHIJKLMNOPQR
1UserTransaction DateDescriptionDebitCreditCategoryAccountDepartmentDepartment 100Department 200
2Brian8/18/2023MOUNTAINS EDGE
26​
3Repairs/Maintenance66200100AccountAmountAccountAmount
3Gabe8/19/2023RBT MARATHON PETRO1014
1​
20050001-100
28.37​
50001-200
0​
4Gabe8/19/2023RBT CIRCLE K # 23710
0.32​
20065000-100
63.95​
65000-200
0​
5Gabe8/22/2023CIRCLE K # 23706
50.02​
20065500-100
617.45​
65500-200
0​
6Gabe8/22/2023WALMART.COM
39.47​
20065600-100
172.46​
65600-200
53.25​
7Brian8/23/2023SONIC DRIVE IN #3236
50​
Business Meals6715010066200-100
26​
66200-200
52.05​
8Darius8/23/2023BOXLEY READY MIX
617.45​
Advertising6550010066425-100
289​
66425-200
0​
9Gabe8/23/2023WYTHEVILLE RURAL KING
459.06​
20067150-100
50​
67150-200
0​
10Gabe8/23/2023CIRCLE K # 23705
86​
20067400-100
28.42​
67400-200
0​
11Brian8/24/2023LOWES #01164*
28.37​
Cost of Goods Sold5000110067575-100
608.75​
67575-200
0​
12Nolan8/24/2023MARATHON PETRO213082
53.25​
Fuel65600200
13Gabe8/24/2023CIRCLE K # 23705
105.07​
200Statement Total$ 1,989.70
14Darius8/25/2023******* Barrel
100​
Business Meals67150100
15Gabe8/25/2023RBT CIRCLE K # 23706
0.5​
200
16Gabe8/26/2023RBT CIRCLE K # 23705
0.86​
200
17Gabe8/27/2023AMAZON.COM*T317H4PH0 A
21.03​
200
18Gabe8/27/2023RBT CIRCLE K # 23705
1​
200
19Gabe8/27/2023AMZN Mktp US*T38BE4XB2
223.02​
200
20Rich L.8/28/2023CASEYS #2840
84.76​
200
21Rich L.8/28/2023SHEETZ 0407 00004077
75.78​
200
22Rich L.8/28/2023SHELL OIL 10003719001
63.04​
200
23Darius8/28/2023DOMAIN NETWORKS
289​
IT66425100
24Daniel8/28/2023DUBLIN LIBERTY
172.46​
Fuel65600100
25Daniel8/28/2023DUBLIN LIBERTY
101.52​
Fuel65600100
26Gabe8/28/2023USPS PO 5199301382
17.95​
200
27Gabe8/28/2023CIRCLE K # 23708
79​
200
28Gabe8/28/2023LOWES #01164*
68.66​
200
29Rich L.8/29/2023SCHEELS COLORADO SPRIN
720.96​
200
30Rich L.8/29/2023CENEX JJ TRAVE09913559
82.24​
200
31Darius8/29/2023VA STONE AND BRICK DES
2422.65​
Advertising65500100
32Nolan8/29/2023WAL-MART #3270
52.05​
Repairs/Maintenance66200200
33Daniel8/29/2023AMAZON.COM*T38037SJ0 A
37.9​
Repairs/Maintenance66200100
34Gabe8/29/2023PARTSWH COM
74.81​
200
35Rich L.8/30/2023LOVE'S #0873 OUTSIDE
95.28​
200
36Darius8/30/2023Amazon.com*T35UU9I50
63.95​
Office Supplies65000100
37Daniel8/31/2023WYTHEVILLE RURAL KING
23.35​
Repairs/Maintenance66200100
38Daniel8/31/2023SQ *MEREDITHS GARAGE
20​
Repairs/Maintenance66200100

Ok, rows 2, 33, 37, and 38 all match a criteria for N7 so the total for O7 should subtract the corresponding H cells and add the corresponding G cells to return the total of 104.25
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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