Solved

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

Posted on 2011-05-06
256 Views
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
0
Question by:activematx

LVL 33

Accepted Solution

Use a formula like this:

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

jppinto
SUMPRODUCT2.xlsx
0

LVL 9

Author Closing Comment

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

LVL 33

Expert Comment

0

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
``````

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

LVL 9

Expert Comment

Nevermind! Always refresh the page before submitting :)
0

LVL 33

Expert Comment

0

Featured Post

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.