sum if question

[carl]

My data looks like so:

ColA ColB
abc441 10
def232 20
abc221 10
XYZ220 30

I would like to create a sumif that will look at the left
3 characters in ColA, compare to the below table , and if
there is a match, sum.

abc
def

Thank you in advance.

 

[Domenic]

Hi Carl,

Using your table, and assuming that Cell A7 contains abc and Cell A8
contains def, and so on, try putting this formula in B7 and copy down:

=SUMIF($A$1:$A$4,"abc*",$B$1:$B$4)

Hope this helps!

 

[Peo Sjoblom]

Assume you put abc in E1 and the example table in A1:B4


=SUMPRODUCT(--(LEFT($A$1:$A$4,3)=E1),$B$1:$B$4)

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Andy B]

Hi

How about something like:
=SUMPRODUCT((LEFT(A2:A5,3)={"abc","def"})*(B2:B5))