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:

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

<tbody>
</tbody>

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?