Help with VLOOKUP + SUM and COUNTIF

M

MikeJHHT

New Member
Joined
Jan 8, 2020
Messages
39
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Try Sumif

=SUMIF(COMBO!B:B,"A1*",COMBO!C:C)+SUMIF(COMBO!B:B,"A2*",COMBO!C:C)
 
Upvote 0
A shortcut way to write that formula:

=SUM(SUMIF(COMBO!B:B,{"A1*","A2*"},COMBO!C:C))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Review, critique, help a newb
Replies
6
Views
933
bamblack
bamblack

Forum statistics

Threads
1,214,424
Messages
6,119,404
Members
448,893
Latest member
AtariBaby

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