Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Summing Based on Part of the Information in a Cell.

Summing Based on Part of the Information in a Cell

by Allen Wyatt
(last updated June 1, 2015)

1

Kathy has a worksheet that includes information for all the parts in her warehouse. In this sheet, part numbers are shown in column A using the format 12345 XXX, where XXX represents a location code. This means she could have multiple entries on the worksheet for the same part numbers, but each entry representing a different location for that part. Kathy needs a formula that sums the values associated with each part number, regardless of its location code. Thus, she needs a way to sum the quantity column related to parts 12345 ABC, 12345 DEF, 123456 GHI, etc. She needs a way to do this without splitting the location code to a different column.

There is more than one way to get the desired answer. For the sake of the examples in this tip, assume that the part numbers are in column A (as Kathy indicated) and that the quantities for each part are in column B. It is these quantities that need to be summed, based upon just a portion of what is in each cell in column A. Further, you can put the part number (minus the location code) desired in cell D2.

The first potential solution is to use the SUMPRODUCT function, in this manner:

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

This formula checks the values in the range A2:A49. You should make sure that this range reflects the range of your actual data. If you generalize the formula so that it looks at all of columns A and B (as in A:A and B:B), you'll get a #VALUE error, since it tries to apply the formula to empty cells in the columns.

You can get a similar result by using an array formula such as this:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

Remember, again, that this is an array formula, so you need to enter it by pressing Shift+Ctrl+Enter. Note, as well, that this formula converts the value in D2 to text for the comparison. This wasn't done in the previous formula because there the substring picked out of column A was converted to a numeric value using the VALUE function.

You can also use the DSUM function to construct a working formula. Let's assume that the part numbers (column A) have a column header in cell A1. Copy this column header (such as "Part Num") to another cell in the worksheet, such as cell D1. In cell D2, enter the part number, without its location code, followed by an asterisk. For example, you could enter "12345*" (without the quote marks) into cell D2. With that specification set up, you can then use this formula:

=DSUM($A$1:$B$49,$B$1,D1:D2)

This formula uses the specification in cell D2 (the characters 12345 followed by anything) as a key to which values from column B should be summed.

Finally, if you had the same specification in cell D2 as you used with the DSUM approach, you could use a very simple SUMIF function, in this manner:

=SUMIF(A:A,D2,B:B)

Note that this approach allows you to use the full column ranges (A:A and B:B) in the formula.

If your part numbers (in column A) are not as consistent in their format as you might like, then you may be better creating a user-defined function to find your quantities. For instance, if your part numbers aren't always the same length or if the part numbers can contain both digits and letters or dashes, then a UDF is the way to go. The following example works great; it keys on the presence of at least one space in the value. (Kathy indicated that a space separated the part number from the location code.)

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _
  FindPart As Variant) As Long
    Dim Pos As Integer
    Dim Pos2 As Integer
    Dim i As Long
    Dim tmp As String
    Dim tmpSum As Long
    Dim PC As Long

    PC = Parts.Count
    If PartsQty.Count <> PC Then
        MsgBox "Parts and PartsQty must be the same length", vbCritical
        Exit Function
    End If

    For i = 1 To PC
        Pos = InStr(1, Parts(i), " ")
        Pos2 = InStr(Pos + 1, Parts(i), " ")

        If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then
            tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))
        ElseIf Pos > 0 And Len(Parts(i)) > 0 Then
            tmp = CStr(Trim(Left(Parts(i), Pos - 1)))
        End If

        If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then
            tmpSum = tmpSum + PartStock(i)
        End If
    Next i

    AddPrtQty = tmpSum
End Function

To use the function, in your worksheet call it using two ranges and the part number you want:

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11468) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Summing Based on Part of the Information in a Cell.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Editing a Scenario

Once a scenario is defined and saved, you can later revisit the values you created for the scenario and modify them. Here's ...

Discover More

Changing the Number of Columns

If you need to change the number of columns used in a portion of your document, it's easy to do when you use the Columns ...

Discover More

Testing if a Workbook is Open

Your macros can easily open and manipulate other Excel workbooks. If a workbook you are trying to use is already in use by ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

MORE EXCELTIPS (MENU)

Applying Range Names to Formulas

If you define your named ranges after you create your formulas, you can have Excel update those formulas to reflect the newly ...

Discover More

Last Non-Zero Value in a Row

If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a ...

Discover More

Excluding Values from Averaging

Calculating an average of a group of numbers is easy. What if you want to exclude a couple of the numbers from the group you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 0?

2012-03-19 14:39:33

Lesley O

Regarding the SUMIF example above:
If you just want to put "12345" into cell D2, you can add the asterisk using concatenate within your SUMIF formula:
=SUMIF(A:A,concatenate(D2,"*"),B:B)
If you have extra characters in front of your part number (A12345, P12345), you can add that extra * using the same function:
=SUMIF(A:A,concatenate("*",D2,"*"),B:B)
It may be handier to add the * this way (as opposed to directly into cell D2), if you need to reference the part number in cell D2 in another formula, etc.


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share