How to add a sum that matches certain text criteria

[Hiltsy]

Hi all
Im looking to work out how i can a create a value/sum based on text

So as per my example Set A would need to multiply the total by 4 and again in a separate column by 6
Set B would have a different criteria being multiplied by 2 and next column by 4 and so on

i would do this manually, but i have around 10 different sets and thousands of lines

Hope the above makes some sense to you wizards, as im relatively new to excel, and probably like most newbies can work out a lot of problems
but this one has me stumped

Many Thanks
Hiltsy

[AliGW]

The easiest way would be to add two helper columns with lookup formulae to add the lower and upper multipliers from a lookup list, then it would simply be a case of applying these to a sum (multiplication).

Columns C and D could be hidden, of course.

[JohnTopley]

In E2

=$D2*(LOOKUP(RIGHT($B2,1),{"A","B","C"},{4,2,6}))

in F2

=$D2*(LOOKUP(RIGHT($B2,1),{"A","B","C"},{6,4,8}))

[Hiltsy]

Thankyou, thats worked a treat

[AliGW]

Which solution did you go with? John's is neat.

Thanks for the rep.

[bebo021999]

Single formula in E2, drag down and accross

Please Login or Register  to view this content.

[Hiltsy]

Which solution did you go with? John's is neat.

Thanks for the rep.

i opted for your way, i tried both, and they both work equally well, but with my lack of knowledge and the amount of sets (hundreds) i found your way easier
and no problem, thanks again

[AliGW]

Yes, I suspected the number of sets would swing it one way or the other. Thanks for letting us know.