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

Posted on 2011-05-06
Last Modified: 2012-05-11
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 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.
Question by:activematx
    LVL 33

    Accepted Solution

    Use a formula like this:


    Please check the attached file.

    LVL 9

    Author Closing Comment

    Fast response.  This guy is a great asset to the community!
    LVL 33

    Expert Comment

    Why a B grade?
    LVL 9

    Expert Comment

    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.
    LVL 9

    Expert Comment

    Nevermind! Always refresh the page before submitting :)
    LVL 33

    Expert Comment

    If the answer was a "Fast response", why the B grade?!?

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now