Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Summing Based on Part of the Information in a Cell.
Written by Allen Wyatt (last updated July 22, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, 12345 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. If you are using the latest versions of Excel, that doesn't really mean much, as you would simply enter the formula regularly. If you are using an older version of Excel, however, you need to specifically enter it as an array formula, meaning you 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")
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11469) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Summing Based on Part of the Information in a Cell.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Remember your number line from your early years in school? Some numbers can be below zero (negative numbers) and others ...
Discover MoreSometimes it is helpful to look at data that is rotated 90 degrees. Excel allows you to use Paste Special to transpose ...
Discover MoreReference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-07-25 10:03:47
J. Woolley
@DIMITRIS
Try changing PartStock(i) to Parts(i), but I haven't tested this.
2023-07-24 05:37:02
DIMITRIS
IN THE ABOVE CODE THERE IS A PROBLEM FOR ME. WHEN I PUT THE FUNCTION A MSGBOOX APPEARANCE : PARTSTOCK SUB OR FUNCTION NOT DEFINED.
THANK YOU!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments