1. ## 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. Are the account numbers all at least 4 digits before the D?

3. 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.

4. 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.

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

6. Hi RG

Thanks for the help, much appreciated

Regards

Howard

7. Hi Rory

All the numbers before the D are allways four digits

8. 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.

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