Sum quantity of first column

**esential** · 10-06-2009, 07:24 AM

I have 3 columns:
in first cell (A1) i have a code: 553482300040
in second cell (B1) quantity: 1000
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.

What i have to do is to sum the quantity of the products in third cell, row 1 (A3).
Can someone help me?

Thank you!

**esential** · 10-06-2009, 07:32 AM

to be more explicite:

Code______________Quantity

553282300000________ 50______________55
553290240040________160______________58
553480480020________420______________7E
553492300040________ 20
583182300060SMA____100
585281100060SMA____ 80
7E1382300000________15
7E1682300000________38
7E3684000000________73
7E3684000002________57

**NBVC** · 10-06-2009, 07:58 AM

=Sumif($A$1:$A$100,C1&"*",$B$1:$B$100)

where A1:A100 hold codes and B1:B100 hold quantities

C1 holds 2-digit code "55"

Copy formula down.

**esential** · 10-06-2009, 08:07 AM

can i make this in macro?

**daddylonglegs** · 10-06-2009, 08:08 AM

Note that SUMIF with a wildcard will only work if column A values are text. If they are numbers try

=SUMPRODUCT((LEFT(A$1:A$100,2)=C1&"")+0,B$1:B$100)

**NBVC** · 10-06-2009, 08:18 AM

Originally Posted by esential

can i make this in macro?

If you wanted a macro, you needed to post in the Programming forum and explicitly say you want a macro to do this....

Please post in the Programming forum.

**esential** · 10-06-2009, 08:25 AM

Thank you! Both functions work perfectly

I posted this on Programing forum, but someone moved it.

**NBVC** · 10-06-2009, 08:27 AM

Perhaps because the question didn't mention you wanted a macro to do the job... and it looks like you are asking for a formula.

**DonkeyOte** · 10-06-2009, 08:28 AM

And for the record I did not move the thread ... were your thread moved you would traditionally be notified as such.

**daddylonglegs** · 10-06-2009, 02:18 PM

Technically, as "Excel Worksheet Functions" is a sub-forum of "Excel Programming" then the thread still is "in the programming forum". I've never really understood why it's structured that way........