SUMIFS criteria 1st through 3rd digits =??

[jkream]

I am writing a macro to return the sum of the amounts in a column C (CHECK_AMT) when the criteria in column B are met. I am having trouble differentiating the column B (FILENO) results:

[TABLE="width: 149"]
<tbody>[TR]
[TD]FILENO[/TD]
[TD]CHECK_AMT[/TD]
[/TR]
[TR]
[TD]100X2943[/TD]
[TD="align: right"]175[/TD]
[/TR]
[TR]
[TD]100X3076[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]100X692[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]11BB47[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]1B1083[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]1B1266[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]1B992[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]227X9[/TD]
[TD="align: right"]617[/TD]
[/TR]
[TR]
[TD]24X108[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]336X1301[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]336X1683[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]336X2037[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]362X12[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]369X1549[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]369X2442[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]425A2993[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]425A3090[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]425A3202[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]425B272[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]4AA4294[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]4AA1868[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]4AA2367[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]4BB1289[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]4BB1326[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]4CC1115[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]4CC1376[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]4CC1456[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD]4CC912[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]4DD135[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]4DD1612[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]4DD279[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]4DD536[/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD]4DD545[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]4DD999[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]4Z206[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]4Z75[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]4Z76[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]900G1161[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]900G1259[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]900G18[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]90X37[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

The first set of numbers in the file number is the client, the rest is useless. I need to sum by client. The number of rows will be variable, so I would like to select ALL of rows in each column without hard coding the range.
ie: I need to sum all the values if the file number begins with ONLY a 4, NOT 426 or 425, or 90, but NOT 900, 1,11, and 100 are all to be separate.
For this I am only summing the 4's and the 900's but show me once and I can expand on it if I need to.
I am writing this in Excel 2013, but one of my users is running 2003 and it has to be compatible.
This is my first attempt at VB Macros. Any help is appreciated.

 

[Norie]

Can you post your current code and explain how it isn't working as expected/wanted?

 

[Scott Huish]

This will execute faster if you can specify some sort of reasonable range rather than whole columns, but try this:

=SUMPRODUCT(--(LEFT(A:A)="4"),1-(ISNUMBER(MID(A:A,2,1)+0)),B:B)

 

[jkream]

Can you post your current code and explain how it isn't working as expected/wanted?

This worked until I brought it over to Excel 2003, but it crashed on SortOn:=xlSortOnValues?

Sub Sort2()
'
' Sort2 Macro
'

'
Cells.Select

Dim sSheetName As String
Dim sDataRange As String

sSheetName = ActiveSheet.Name
sDataRange = Selection.Address

ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Add Key:=Range _
("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(sSheetName).Sort
.SetRange Range(sDataRange)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Cells.EntireColumn.AutoFit
End Sub

 

[jkream]

This works GREAT in the Cell, how do I put this in a Macro? I get a Compile Error for:

Sub Test()
Range("K4").Select
Selection.NumberFormat=SUMPRODUCT(--(LEFT(A:A)="4"),1-(ISNUMBER(MID(A:A,2,1)+0)),B:B)

End Sub

 

[Scott Huish]

No need to Select, and it isn't a number format:

Range("K4").Formula = "=SUMPRODUCT(--(LEFT(A:A)=""4""),1-(ISNUMBER(MID(A:A,2,1)+0)),B:B)"

 

[jkream]

Thank you Scott. It won't surprise you, but I'm very psyched that this works! And its compatible with 2003 as well?

You are right about selecting the whole column slowing it down. Is there a way to make it dynamic, so it selects only the cells in the columns that are being used?

Would you mind explaining to me what this line does exactly? It works for FILENO "4" but not for "900" I assume it is the number of digits you are looking at? But I am unsure of how this line is selecting the digits? This way I could adapt it for any number of digits I needed in the future.

 

[jkream]

Thank you Scott. It won't surprise you, but I'm very psyched that this works! And its compatible with 2003 as well?

You are right about selecting the whole column slowing it down. Is there a way to make it dynamic, so it selects only the cells in the columns that are being used?

Would you mind explaining to me what this line does exactly? It works for FILENO "4" but not for "900" I assume it is the number of digits you are looking at? But I am unsure of how this line is selecting the digits? This way I could adapt it for any number of digits I needed in the future.

 

[Scott Huish]

Range("K4").Formula = "=SUMPRODUCT(--(LEFT(A:A)=""4""),1-(ISNUMBER(MID(A:A,2,1)+0)),B:B)"

If you wanted to check for 90, change ""4"" to ""90"", change the ISNUMBER(MID to start at the 3rd character, so ISNUMBER(MID(A:A,3,1)

Excel 2003 doesn't support whole column ranges for SUMPRODUCT, you will have to use a range, and yes there is a way.

Sub test()
Dim x As Long
x = Range("A" & Rows.Count).End(xlUp).Row
Range("K4").Formula = "=SUMPRODUCT(--(LEFT(A1:A" & x & ")=""4""),1-(ISNUMBER(MID(A1:A" & x & ",2,1)+0)),B1:B" & x & ")"
End Sub

 

[jkream]

this new script works for 4
Does not work for 90 changing the 2 to a 3
Or for 900 changing the 2 to a 4?