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
activematxAsked:
Who is Participating?
 
jppintoCommented:
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
 
activematxAuthor Commented:
Fast response.  This guy is a great asset to the community!
0
 
jppintoCommented:
Why a B grade?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
VampireofdarknessCommented:
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

Open in new window


I'm not fantastic with VBA, unfortunately, but it appears to work.
0
 
VampireofdarknessCommented:
Nevermind! Always refresh the page before submitting :)
0
 
jppintoCommented:
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.

All Courses

From novice to tech pro — start learning today.