Vlookup and left function
 

I have 2 columns:

Model qty
HAC4##### 1
HHP4##### 2
HXA4##### 1
HXA2##### 3
HXH2##### 3
H9MPV#### 4
chair 1
toys 1
benchmark 1

HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
looking for. I would like to write an equations that looks for these common
identifiers and muliplies 40 x the QTY column.

Vlookup and left function
 

Hi!

So, you want the total of ALL entries that match the the variables?

Make a list of those variables:

F1:F6 =

HAC4
HHP4
HXA4
HXA2
HXH2
H9MPV


Then:

=SUMPRODUCT(--(ISNUMBER(SEARCH(F1:F6,A1:A9))),B1:B9)*40

Biff

"Corey Osborn" <Corey wrote in message
...
I have 2 columns:

Model qty
HAC4##### 1
HHP4##### 2
HXA4##### 1
HXA2##### 3
HXH2##### 3
H9MPV#### 4
chair 1
toys 1
benchmark 1

HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
looking for. I would like to write an equations that looks for these
common
identifiers and muliplies 40 x the QTY column.

Vlookup and left function
 

Do you mean to look them up one by one?

=VLOOKUP("HXA2*",A2:B10,2,0)*40

would return 120 using your example

or

=VLOOKUP(C1&"*",A2:B10,2,0)*40

where you would put the criterion in C1

or do you mean that you want to total the whole range, sum the respective
numbers and multiply the total with 40? Using your example would be 14*40 =
560 If the latter use

=SUMPRODUCT(SUMIF(A2:A100,{"HAC4";"HHP4";"HXA4";"H XA2";"HXH2";"H9MPV"}&"*",B2:B100))*40



--

Regards,

Peo Sjoblom




"Corey Osborn" <Corey wrote in message
...
I have 2 columns:

Model qty
HAC4##### 1
HHP4##### 2
HXA4##### 1
HXA2##### 3
HXH2##### 3
H9MPV#### 4
chair 1
toys 1
benchmark 1

HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
looking for. I would like to write an equations that looks for these
common
identifiers and muliplies 40 x the QTY column.

Vlookup and left function
 

i assume the HAC4##### indicates there are multiple items of HAC4, the
easiest way would be to sort then just multiply them as they "sit".
"Corey Osborn" wrote:

I have 2 columns:

Model qty
HAC4##### 1
HHP4##### 2
HXA4##### 1
HXA2##### 3
HXH2##### 3
H9MPV#### 4
chair 1
toys 1
benchmark 1

HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
looking for. I would like to write an equations that looks for these common
identifiers and muliplies 40 x the QTY column.