Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Adding values based on a certain criteria

    I have account numbers in Col A and values in Col B

    I would like to add up all the values in Col B where the account number in Col A ends in a D , where trhe account number >2999D and < 3015D

    Your assistance in this regard is most appreciated

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are the account numbers all at least 4 digits before the D?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Here's a user defined function that will accomplish the task.
    VBA UDF Sum By AcctNo.JPG
    Code:
    Option Explicit
    
    Private Function lSumByAcctNo(rngAcctNos As Range, rngData As Range) As Long
    
       Dim rCell As Range
       
       lSumByAcctNo = 0
    
       For Each rCell In rngAcctNos.Cells
       
          If Right(rCell, 1) = "D" And _
             Val(Left(rCell, 4)) < 3016 And _
             Val(Left(rCell, 4)) > 2998 Then
            lSumByAcctNo = lSumByAcctNo + rCell.Offset(0, 1).Value
          End If
       
       Next rCell
          
    End Function
    Assumptions:
    1. The numbers to be summed contain no decimal points. If they do change the "as Long" on the Function line to Double or Currency.
    2. The range to be summed is the column to the right of the Acct numbers.
    3. All account numbers are 4 numbers and 1 letter.

    Note: the second range although not actually used in the body of the function is there so that the function will recalc automatically when one of the values is changed otherwise it would only auto recalc when an account number was changed.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    RG,
    Why not use the second range since it's supplied? Better practice than using offset, especially as the sum range might not be one to the right.
    You could also use SUMPRODUCT.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Here's a new and improved version that actually uses the data range to calculate the offset thus allowing the data to be summed to be in any column to the right of the Acct Nos column.
    VBA UDF Sum By AcctNo V2.JPG
    Code:
    Private Function lSumByAcctNoV2(rngAcctNos As Range, rngData As Range) As Long
    
       Dim rCell      As Range
       Dim lColOffset As Long
       
       lColOffset = rngData.Cells(1, 1).Column - rngAcctNos.Cells(1, 1).Column
       lSumByAcctNoV2 = 0
    
       For Each rCell In rngAcctNos.Cells
       
          If Right(rCell, 1) = "D" And _
             Val(Left(rCell, 4)) < 3016 And _
             Val(Left(rCell, 4)) > 2998 Then
            lSumByAcctNoV2 = lSumByAcctNoV2 + rCell.Offset(0, lColOffset).Value
          End If
       
       Next rCell
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the help, much appreciated

    Regards

    Howard

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Rory

    All the numbers before the D are allways four digits

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    SP version: =SUMPRODUCT((RIGHT(A1:A11)="D")*(LEFT(A1:A11,4)>"2 999")*(LEFT(A1:A11,4)<"3015"),B1:B11)
    SUMIFS version (if XL2007+): =SUMIFS(B1:B11,A1:A11,"????D",A1:A11,">2999D",A1:A 11,"<3015D")

    The SP version assumes you couldn't have 3011ED for example. You could add a length test if need be.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Rory

    Thanks for the help, much appreciated

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •