Help with VLOOKUP + SUM and COUNTIF

MikeJHHT

New Member
Joined
Jan 8, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
What im looking at is counting a range of cells based off there 2 first characters...=COUNTIF(COMBO!B:B,"A1*")+COUNTIF(COMBO!B:B,"A2*")
now in column C there is a quantity next column B, I need to add up the quantities that go with the results giving from totaling all cells that have A1 and A2
same with A3-A4,, C1-C2 etc



Zone Forecast.xlsx
ABCDEFGH
10000098V14722
2AFWTMPBA1000A5
3AFRDMPAA1044A1
4AFRHMPAA1056A4RYAN
5AFRHMPBA2000A2YANKEE
6AFKDMPBA2009A2BULK
7AFWTMPCA2028A1OUTLAW
8ODCOUG-36PHA3006A1V1
9ODVILLAG-36TA3028A1V2
108000CLX-IFTLP-GA4010A1V3+VB
11TRUE-36S-IFTA4027A1STONE
12TRUE-42TG-IFTA4031A1
13SLP-TRAP2C1000B7
14DVP48C10206
15DVP-TRAP2C103036
16SLP48C105030
17SL1148C10603
18DVP36C10702
19DVP-TRAPK2C10801
20SLP-TVHWC10909
21LINK-DV30BC11201
22DV4236-BC12109
23NDV3933IL-BC13401
24DVP-TVHWC2000A2
25DVP-TRAP1C2000B4
26DV3732-BC203036
27SUPREME-I30-IFTC20502
28SUPREME-I35-IFTC20605
296000CLX-IFT-SC20801
30DV3732L-BC20908
31ESCAPE-35-IFTC21301
326000CLX-IFTLP-SC21605
33SL-7LPC21707
34NDV3933I-BC23001
35SL-7XLP-IFTC2320B2
36SL-5XLP-IFTC2340A1
376000CLX-IFTLP-GC2350A1
386000C-IFTLPC2370A3
396000CLX-IFT-TGC2370B1
40SL-7C30202
41NDV4236IL-BC30304
42COSMO42-IFT-BC30402
431-70-784235-1C30454
44SANTAFE-CC30602
451-90-06100-1C30903
46MONTP-II-CBC31101
47PS35-CC31501
48EXPEDITION-IIC3220A1
49SL-5C3250A3
50SL-7X-IFTC3270A2
51SL-9X-IFTC3290B1
522044-CAT-CC3350B3
531-90-584241-1C40204
541-90-999000-1C40602
55CAB50-CC40901
56CASTILE-MBK-CC41001
571-90-680001-14C41301
58DISCOVERY-II-CC41502
591-90-00680-1C41701
602040-CAT-CC42201
61CB1200-CC42401
62CRAVE6048-CC43501
63MBAGMS2F101212
64MBSR70X20-CF101525
65MBSR57X8-CS400125
66HARC-MI30-NBV00332
67MI35-5040CS-BKV00531
68MI30-4230-NBV00922
690000127V01113
70FOL-36-BKV100113
71FS-7-BKV101114
72MI35-4229-NBV10421
73FS-MI35-BKV10471
74SIM-4236V10915
75FS-5-BKV11313
76MI35-4432-BKV11381
771-00-574323V11511
78BRICK-SL7V11586
79MI35-4832-BKV11671
80HAL-36-BKV11711
811-00-774257V11851
82SIM-42C-BKV11911
83ARC-36-GTV11971
84FSI-36-BKV12011
85SLP-WT-BKV12041
8630006623V12844
87ARC-42-BKV13011
88BRICK-MI35-TRV13161
COMBO
Cell Formulas
RangeFormula
A1A1='SO PASTE'!A1
B1:B88B1=VLOOKUP(A1,Inventory!A:I,9,FALSE)
C1:C88C1=VLOOKUP(A1,'SO PASTE'!A:C,3,FALSE)
A2A2='SO PASTE'!A72
A3:A5,A7A3='SO PASTE'!A69
A6A6='SO PASTE'!A68
A8:A9A8='SO PASTE'!A178
A10,A71A10='SO PASTE'!A64
A11:A12A11='SO PASTE'!A225
A13A13='SO PASTE'!A202
A14A14='SO PASTE'!A111
A15A15='SO PASTE'!A105
A16A16='SO PASTE'!A211
A17A17='SO PASTE'!A199
A18,A79A18='SO PASTE'!A109
A19A19='SO PASTE'!A106
A20A20='SO PASTE'!A204
A21,A46A21='SO PASTE'!A147
A22A22='SO PASTE'!A102
A23A23='SO PASTE'!A174
A24A24='SO PASTE'!A107
A25A25='SO PASTE'!A104
A26A26='SO PASTE'!A100
A27:A28A27='SO PASTE'!A223
A29,A32A29='SO PASTE'!A60
A30A30='SO PASTE'!A101
A31A31='SO PASTE'!A115
A33,A35A33='SO PASTE'!A194
A34A34='SO PASTE'!A173
A36A36='SO PASTE'!A192
A37A37='SO PASTE'!A62
A38A38='SO PASTE'!A59
A39A39='SO PASTE'!A61
A40A40='SO PASTE'!A197
A41,A47A41='SO PASTE'!A175
A42A42='SO PASTE'!A90
A43,A45A43='SO PASTE'!A30
A44A44='SO PASTE'!A185
A48A48='SO PASTE'!A116
A49A49='SO PASTE'!A193
A50A50='SO PASTE'!A195
A51A51='SO PASTE'!A198
A52A52='SO PASTE'!A44
A53A53='SO PASTE'!A34
A54A54='SO PASTE'!A39
A55:A56A55='SO PASTE'!A83
A57A57='SO PASTE'!A36
A58A58='SO PASTE'!A94
A59A59='SO PASTE'!A31
A60A60='SO PASTE'!A43
A61A61='SO PASTE'!A85
A62A62='SO PASTE'!A91
A63A63='SO PASTE'!A156
A64A64='SO PASTE'!A160
A65A65='SO PASTE'!A159
A66A66='SO PASTE'!A138
A67A67='SO PASTE'!A171
A68A68='SO PASTE'!A166
A69A69='SO PASTE'!A2
A70A70='SO PASTE'!A122
A72A72='SO PASTE'!A167
A73A73='SO PASTE'!A126
A74A74='SO PASTE'!A190
A75A75='SO PASTE'!A124
A76A76='SO PASTE'!A168
A77,A81A77='SO PASTE'!A22
A78A78='SO PASTE'!A82
A80A80='SO PASTE'!A137
A82A82='SO PASTE'!A191
A83A83='SO PASTE'!A74
A84A84='SO PASTE'!A128
A85A85='SO PASTE'!A205
A86A86='SO PASTE'!A54
A87A87='SO PASTE'!A75
A88A88='SO PASTE'!A81
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Try Sumif

=SUMIF(COMBO!B:B,"A1*",COMBO!C:C)+SUMIF(COMBO!B:B,"A2*",COMBO!C:C)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,471
A shortcut way to write that formula:

=SUM(SUMIF(COMBO!B:B,{"A1*","A2*"},COMBO!C:C))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,744
Members
412,338
Latest member
ebf6543
Top