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.

 

[Scott Huish]

Oh sorry I forgot a part, you have to change the length for LEFT to accommodate the length of the number

Range("K4").Formula = "=SUMPRODUCT(--(LEFT(A1:A" & x & ",2)=""90""),1-(ISNUMBER(MID(A1:A" & x & ",3,1)+0)),B1:B" & x & ")"

 

[jkream]

Oh sorry I forgot a part, you have to change the length for LEFT to accommodate the length of the number

Range("K4").Formula = "=SUMPRODUCT(--(LEFT(A1:A" & x & ",2)=""90""),1-(ISNUMBER(MID(A1:A" & x & ",3,1)+0)),B1:B" & x & ")"

This is Awesome, thank you so much for your effort.