Using LEFT Function On a Range Instead of A Single Cell Value

J

Jiraya_00

New Member
Joined
Oct 18, 2024
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

I'm currently running into a situation where I'm using a SUMIFS formula to sum based on a set of three criteria that exists between two worksheets within a workbook. One of the criteria involves text where on one worksheet the text is 25 characters long and on the other worksheet that has my SUMIFS formula is only 20 characters long. I thought I could wrap that one criteria into a LEFT function that would only look at the first 20 characters in order to perform the match and then sum, but I keep running into an error.

Can anyone help me overcome this error some how?

Screenshot 123138.png


Screenshot 123345.png


Screenshot 123516.png

Book4 - Test.xlsx
ABCDE
1NumberNameRep1Paid AmountTest_Data Amount
2576916Customer A - Test CoRep A1,723.600.00
3578107Customer URep AB2,752.802,752.80
4580390Customer ARep AB1,016.801,016.80
5582249Customer ARep A2,418.002,418.00
6583044Customer ARep AB1,822.801,822.80
7586326Customer URep AB2,021.202,021.20
8587795Customer ARep AB2,777.602,777.60
9587856Customer ARep AB248.00248.00
10589448Customer ARep AB1,488.001,488.00
11591246Customer ARep A930.00930.00
12594666Customer BRep F919.80919.80
13595592Customer BRep F1,209.601,209.60
14596598Customer BRep F415.80415.80
15602919Customer C - Test CoNo Rep882.000.00
16605254Customer CNo Rep1,373.401,373.40
17607582Customer DRep F0.004,158.00
18606435Customer DRep F0.001,197.00
19607693Customer FRep D1,312.001,512.00
20607388Customer IRep A982.80982.80
21608791Customer GNo Rep529.20529.20
22609018Customer ERep D2,935.802,935.80
23612210Customer ARep C1,423.801,423.80
24610144Customer JRep D529.20529.20
25610154Customer JRep BC970.20970.20
26610856Customer E - Test CoRep D2,079.000.00
27610418Customer KRep D997.60997.60
28611763Customer YRep F1,373.401,373.40
29613097Customer HRep E1,839.601,839.60
30611574Customer XRep D1,751.401,751.40
31612467Customer DRep F1,260.001,260.00
32613731Customer YRep BC1,096.201,096.20
33612406Customer HRep E365.40365.40
34613633Customer CNo Rep1,814.401,814.40
35614567Customer XRep BC378.00378.00
36612504Customer DNo Rep1,436.401,436.40
37614447Customer XRep D508.40508.40
38612224Customer HRep E2,595.602,595.60
39613209Customer HRep E1,713.601,713.60
40614454Customer LRep F529.20529.20
41613226Customer TNo Rep0.002,381.40
42613033Customer TRep C2,709.002,709.00
43613648Customer SRep C1,131.201,131.20
44612981Customer ZRep BC604.80604.80
45613452Customer HRep E2,091.602,091.60
46613856Customer MRep C359.60359.60
47615717Customer LRep D1,486.801,486.80
48613765Customer ZNo Rep831.60831.60
49613862Customer HRep E1,096.201,096.20
50615199Customer TRep D458.80458.80
51613368Customer SRep BC1,020.601,020.60
52615523Customer FRep C2,308.402,308.40
53616648Customer ERep D1,927.801,927.80
54615343Customer SRep D1,289.600.00
55613450Customer GNo Rep781.20781.20
56612849Customer PRep D1,449.001,449.00
57618209Customer QNo Rep1,524.601,524.60
58613924Customer ZRep BC1,562.401,562.40
59616360Customer HRep E2,066.402,066.40
60615589Customer LRep B2,394.002,394.00
61613779Customer QRep C415.80415.80
62614000Customer LNo Rep1,827.001,827.00
63614034Customer RRep D1,612.001,612.00
64615765Customer LNo Rep579.60579.60
65619357Customer HRep E3,110.403,110.40
66616091Customer HRep E2,104.202,104.20
67615309Customer NRep D396.80396.80
68618464Customer PRep C1,345.201,345.20
69614174Customer ZRep BC730.80730.80
70619080Customer GNo Rep1,915.201,915.20
71616085Customer HRep E1,726.201,726.20
72618405Customer PNo Rep1,713.601,713.60
73616693Customer MRep C1,322.401,322.40
74616358Customer PRep C1,686.401,686.40
75618403Customer NRep D1,894.401,894.40
76619850Customer ERep B652.80652.80
77618517Customer ERep D2,726.402,726.40
78620420Customer ORep C2,145.052,845.80
79618196Customer RRep B640.00640.00
80618298Customer LRep D1,164.801,164.80
81619871Customer VRep C2,714.002,714.00
82619882Customer ORep C1,083.001,083.00
83619418Customer F - Test CoNo Rep601.600.00
84619775Customer VRep C1,451.401,451.40
85616370Customer HRep E1,171.801,171.80
86619607Customer ERep D2,393.602,393.60
87619473Customer ERep D1,664.001,664.00
88620410Customer LRep D972.80972.80
89617337Customer VRep D550.40550.40
90617475Customer MNo Rep1,203.201,203.20
91616744Customer HRep E907.20907.20
92619914Customer NRep E660.80660.80
93619726Customer NRep C2,368.802,368.80
94619919Customer PRep D460.80460.80
95619629Customer MRep C1,140.001,140.00
96619259Customer SNo Rep1,713.201,843.20
97617778Customer SRep C396.80396.80
98620408Customer V - Test CoNo Rep1,916.200.00
99617457Customer TRep F294.40294.40
100620564Customer TRep C2,029.202,029.20
Reconciliation
Cell Formulas
RangeFormula
E2:E100E2=SUMIFS(Test_Data!$M:$M,Test_Data!$B:$B,A2,Test_Data!$E:$E,B2,Test_Data!$R:$R,C2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACell ValueduplicatestextNO


Book4 - Test.xlsx
ABEMRST
1Report Submission DateInvoice #Account NameCMA Inv. AmountRep AssignedRep SupervisorDBD Rep
27/27/2023576916Customer A - Test Company$1,723.60Rep ARep ABNo Rep
38/3/2023578107Customer U$2,752.80Rep ABRep ABNo Rep
48/28/2023580390Customer A$1,016.80Rep ABRep ABNo Rep
59/12/2023582249Customer A$2,418.00Rep ARep ABNo Rep
69/20/2023583044Customer A$1,822.80Rep ABRep ABNo Rep
710/17/2023586326Customer U$2,021.20Rep ABRep ABNo Rep
810/31/2023587795Customer A$2,777.60Rep ABRep ABNo Rep
911/1/2023587856Customer A$248.00Rep ABRep ABNo Rep
1011/14/2023589448Customer A$1,488.00Rep ABRep ABNo Rep
1112/7/2023591246Customer A$930.00Rep ARep ABNo Rep
121/5/2024594666Customer B$919.80Rep FRep ABNo Rep
131/22/2024595592Customer B$1,209.60Rep FRep ABNo Rep
141/27/2024596598Customer B$415.80Rep FRep ABNo Rep
153/20/2024602919Customer C - Test Company$882.00No RepNo RepRep BC
164/2/2024605254Customer C$1,373.40No RepNo RepRep BC
174/17/2024607582Customer D$4,158.00Rep FRep ABRep BC
184/13/2024606435Customer D$1,197.00Rep FRep ABRep BC
195/2/2024607693Customer F$1,512.00Rep DRep ABNo Rep
204/30/2024607388Customer I$982.80Rep ARep ABNo Rep
214/30/2024608791Customer G$529.20No RepNo RepRep BC
225/13/2024609018Customer E$2,935.80Rep DRep ABRep BC
235/26/2024612210Customer A$1,423.80Rep CRep ABNo Rep
245/21/2024610144Customer J$529.20Rep DRep BCNo Rep
255/23/2024610154Customer J$970.20Rep BCRep BCNo Rep
265/14/2024610856Customer E - Test Company$2,079.00Rep DRep ABRep BC
275/27/2024610418Customer K$997.60Rep DRep ABRep CD
285/21/2024611763Customer Y$1,373.40Rep FRep BCRep CD
296/4/2024613097Customer H$1,839.60Rep ERep ABRep CD
305/22/2024611574Customer X$1,751.40Rep DRep ABNo Rep
315/28/2024612467Customer D$1,260.00Rep FRep ABRep BC
326/14/2024613731Customer Y$1,096.20Rep BCRep BCNo Rep
336/12/2024612406Customer H$365.40Rep ERep ABRep CD
346/10/2024613633Customer C$1,814.40No RepNo RepRep BC
356/21/2024614567Customer X$378.00Rep BCRep BCNo Rep
365/29/2024612504Customer D$1,436.40No RepNo RepRep BC
376/13/2024614447Customer X$508.40Rep DRep ABNo Rep
386/12/2024612224Customer H$2,595.60Rep ERep ABRep CD
396/5/2024613209Customer H$1,713.60Rep ERep ABRep CD
406/17/2024614454Customer L$529.20Rep FRep BCNo Rep
416/19/2024613226Customer T$2,381.40No RepNo RepRep CD
426/3/2024613033Customer T$2,709.00Rep CRep ABNo Rep
436/8/2024613648Customer S$1,131.20Rep CRep ABNo Rep
446/3/2024612981Customer Z$604.80Rep BCRep BCNo Rep
456/10/2024613452Customer H$2,091.60Rep ERep ABRep CD
466/28/2024613856Customer M$359.60Rep CRep ABRep CD
476/21/2024615717Customer L$1,486.80Rep DRep ABNo Rep
486/10/2024613765Customer Z$831.60No RepNo RepRep BC
496/28/2024613862Customer H$1,096.20Rep ERep ABRep CD
506/27/2024615199Customer T$458.80Rep DRep ABRep BC
516/7/2024613368Customer S$1,020.60Rep BCRep BCNo Rep
526/20/2024615523Customer F$2,308.40Rep CRep ABNo Rep
536/29/2024616648Customer E$1,927.80Rep DRep ABRep BC
547/1/2024615343Customer S - Test Company$1,289.60Rep DRep ABRep BC
556/6/2024613450Customer G$781.20No RepNo RepRep BC
566/18/2024612849Customer P$1,449.00Rep DRep ABNo Rep
577/10/2024618209Customer Q$1,524.60No RepNo RepRep CD
586/10/2024613924Customer Z$1,562.40Rep BCRep BCNo Rep
596/26/2024616360Customer H$2,066.40Rep ERep ABRep CD
606/18/2024615589Customer L$2,394.00Rep BRep ABNo Rep
616/25/2024613779Customer Q$415.80Rep CRep ABNo Rep
626/12/2024614000Customer L$1,827.00No RepNo RepRep CD
636/11/2024614034Customer R$1,612.00Rep DRep ABNo Rep
647/3/2024615765Customer L$579.60No RepNo RepRep CD
657/18/2024619357Customer H$3,110.40Rep ERep ABRep CD
666/21/2024616091Customer H$2,104.20Rep ERep ABRep CD
677/16/2024615309Customer N$396.80Rep DRep ABNo Rep
687/12/2024618464Customer P$1,345.20Rep CRep ABNo Rep
697/2/2024614174Customer Z$730.80Rep BCRep BCNo Rep
707/15/2024619080Customer G$1,915.20No RepNo RepRep CD
716/21/2024616085Customer H$1,726.20Rep ERep ABRep CD
727/12/2024618405Customer P$1,713.60No RepNo RepRep CD
737/1/2024616693Customer M$1,322.40Rep CRep ABNo Rep
747/1/2024616358Customer P$1,686.40Rep CRep ABNo Rep
757/16/2024618403Customer N$1,894.40Rep DRep ABNo Rep
767/18/2024619850Customer E$652.80Rep BRep ABNo Rep
777/16/2024618517Customer E$2,726.40Rep DRep ABRep BC
787/27/2024620420Customer O$2,845.80Rep CRep ABNo Rep
797/10/2024618196Customer R$640.00Rep BRep ABNo Rep
807/10/2024618298Customer L$1,164.80Rep DRep ABNo Rep
817/24/2024619871Customer V$2,714.00Rep CRep ABNo Rep
827/18/2024619882Customer O$1,083.00Rep CRep ABNo Rep
838/16/2024619418Customer F - Test Company$601.60No RepNo RepRep CD
847/23/2024619775Customer V$1,451.40Rep CRep ABNo Rep
856/26/2024616370Customer H$1,171.80Rep ERep ABRep CD
867/21/2024619607Customer E$2,393.60Rep DRep ABRep BC
877/22/2024619473Customer E$1,664.00Rep DRep ABRep BC
887/27/2024620410Customer L$972.80Rep DRep ABNo Rep
897/30/2024617337Customer V$550.40Rep DRep ABRep CD
907/31/2024617475Customer M$1,203.20No RepNo RepRep CD
917/1/2024616744Customer H$907.20Rep ERep ABRep CD
928/7/2024619914Customer N$660.80Rep ERep ABNo Rep
937/23/2024619726Customer N$2,368.80Rep CRep ABNo Rep
947/14/2024619919Customer P$460.80Rep DRep ABNo Rep
957/20/2024619629Customer M$1,140.00Rep CRep ABNo Rep
967/18/2024619259Customer S$1,843.20No RepNo RepRep CD
978/2/2024617778Customer S$396.80Rep CRep ABNo Rep
987/26/2024620408Customer V - Test Company$1,916.20No RepNo RepRep BC
997/30/2024617457Customer T$294.40Rep FRep ABNo Rep
1007/30/2024620564Customer T$2,029.20Rep CRep ABNo Rep
Test_Data
Cell Formulas
RangeFormula
M2:M100M2=(F2-G2-H2-I2-J2-K2-L2)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
How about
Excel Formula: 
=SUMIFS(Test_Data!$M:$M,Test_Data!$B:$B,A2,Test_Data!$E:$E,B2&"*",Test_Data!$R:$R,C2)
 
Upvote 0
Solution
Thank you Fluff! I didn't think about using the wildcard search, but this definitely does the job! :)
 
Upvote 0
other idea if you want to test 20 chars:
Excel Formula: 
=SUM(filter(Test_Data!$M:$M,(left(Test_Data!$B:$B,20)=A2)*(Test_Data!$E:$E=B2)*(Test_Data!$R:$R=C2)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
SUMIFS using Dropdown Menus and Date ranges
Replies
5
Views
876
jorismoerings
J
markkeith
  • Solved
Function or Formula to get a value for each cell from multiple logical test.
Replies
3
Views
667
markkeith
markkeith
B
  • Question Question
Auto Arrange cell values to a specific range with VBA
Replies
2
Views
364
bootdat
B
G
  • Solved
Find a value from the first column of sheet B using 2 values from sheet A. Some cells have a number range (EX: 19-20)
Replies
8
Views
710
Fluff
Fluff

Forum statistics

Threads
1,223,101
Messages
6,170,116
Members
452,302
Latest member
TaMere

Share this page

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