Pretty Easy Excel Formula to combine prices of items if the first 3 digits are the same.

Hi Excel Experts!

Basically, I have items with ID's.  Example:

ITEM ID                  Extended Cost
100293                    \$40
300923                    \$30
100533                    \$8
200533                    \$40
1002433                  \$10
400253                    \$80
2002343                  \$28

If you notice, the first 3 numbers in the item ID is what we use for our categories (100, 200, 300, and 400).

I need a formula that will take the first 3 numbers of the item id and add the extended cost into one final output such as:

100 = 58
200 = 30
300 = 68
400 = 80

I have attached an example of what our actual document looks like when we export it from our software.  If someone can help me with this excel formula I can then write the applescript that our user has to run that would automate this.

Note:  I am running Microsoft Office 2008 and 2011 (which supports Macros). I am on a MAC.

I plan on creating an applescript.app that would automate this.  But if someone could make this into a excel macro that would work for Office 2011 then AWESOME!

Otherwise, I will be fine with just the formula; and write the script myself.  I just suck at excel formulas.  Thanks experts.
Example.xlsx
LVL 9
Who is Participating?

Commented:
Use a formula like this:

=SUMPRODUCT((INT(LEFT(\$C\$13:\$C\$17,3))=C30)*(\$I\$13:\$I\$17))

Please check the attached file.

jppinto
SUMPRODUCT2.xlsx
0

Author Commented:
Fast response.  This guy is a great asset to the community!
0

Commented:
Why a B grade?
0

Commented:
Would something like this suffice?

``````Sub Calc100()
a = 0
b = 0
c = 0
d = 0

For x = 13 To 23
If (Left(Cells(x, 3), 3) = 100) Then a = (a + Cells(x, 9))
If (Left(Cells(x, 3), 3) = 200) Then b = (b + Cells(x, 9))
If (Left(Cells(x, 3), 3) = 300) Then c = (c + Cells(x, 9))
If (Left(Cells(x, 3), 3) = 400) Then d = (d + Cells(x, 9))
Next x

Cells(11, 13) = "100 = " & a
Cells(12, 13) = "200 = " & b
Cells(13, 13) = "300 = " & c
Cells(14, 13) = "400 = " & d

End Sub
``````

I'm not fantastic with VBA, unfortunately, but it appears to work.
0

Commented:
Nevermind! Always refresh the page before submitting :)
0

Commented:
If the answer was a "Fast response", why the B grade?!?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.