Sort cells by first 4 alphabets and Sum

[HMIExcel08]

Hi All,

I have a whole bunch of item numbers in Column A with corresponding quantity in column B. some items in column A are like the ones I attached (with size ends). But these items (eg. ABCD01, 02-05 and ABCD 02-08 are exactly the same thing, only sizes are different). I have thousands of items named like this on my Excel worksheet, which I have to sum the quantity of all items like ABCD01, 02-05 and ABCD 02-08. (Notice that some items name have space between letter and number, some don’t. )

I would really appreciate if someone knows how to organize these items only by the first 4 alphabets and auto sum the quantity if they are the same thing.

Thanks a lot for all your help!


Amy

[NBVC]

You can use Sumif() with wildcards...

e.g. =Sumif(A1:A100,"ABCD*",B1:B100) will sum column B where column A starts with "ABCD"

Note: You can replace "ABCD" with a cell reference containing that string.

[HMIExcel08]

Hi NVBC,

I appreciate for your reply and suggestion. The Sumif formula you suggested only works for one item like "ABCD*", whereas I have thousands of different items suck like "BCDE*" "GDDS*" in column A, I don't think sumif works in this case?

Would you please be more specific how can I use cell reference to only keep the first 4 alphabets in column A? I have never used this function in Excel before.


Thanks again!

[NBVC]

So say you have ABCD, BCDE and GDDS listed separately like in your sample file, say in cells G1, G2 and G3...

then your Sumif() formula would be something like:

=Sumif($A$1:$A$100,G1&"*",$B$1:$B$100) copied down

so the first result would be for all the ABCD*, the second for the BCDE* and the third for teh GDDS*...and so on.