Results 1 to 9 of 9

20120807, 01:39 #1
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 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

20120807, 05:11 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,328
 Thanks
 3
 Thanked 216 Times in 199 Posts
Are the account numbers all at least 4 digits before the D?
Regards,
Rory
Microsoft MVP  Excel

20120807, 09:12 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,894
 Thanks
 420
 Thanked 1,585 Times in 1,434 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
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

20120807, 09:24 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,328
 Thanks
 3
 Thanked 216 Times in 199 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

20120807, 09:24 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,894
 Thanks
 420
 Thanked 1,585 Times in 1,434 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

20120807, 12:09 #6
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Hi RG
Thanks for the help, much appreciated
Regards
Howard

20120807, 12:10 #7
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Hi Rory
All the numbers before the D are allways four digits

20120808, 01:59 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,328
 Thanks
 3
 Thanked 216 Times in 199 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

20120808, 10:48 #9
 Join Date
 Feb 2008
 Posts
 1,546
 Thanks
 138
 Thanked 11 Times in 11 Posts
Hi Rory
Thanks for the help, much appreciated
Regards
Howard