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.

 

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