VBA: Need SUMIF formula where only the first 9 characters match

[Mile029]

Is there a way to have a sumif formula where only the first X number of characters are matching. Example

Column A Column B Column C
1 IS1004790 100
2 IS1004790 100
3 IS1004790 100
4 IS1004790 2 200
5 IS1004790 3 100
6 IS1004790 4 100

My formula in column C is =SUMIF(A:A,A1,B:B), which gives me 300 for rows 1,2,3, but 4 gives me 200 and 5 gives me 100 and 6 gives me 100.

I want it to sum all 6 rows because I don't care about the last number. Basically total to 700 in each row in column C. I want the first nine characters to match to do the sum, not an exact match to do the sum. Any help?
edit: attached sample, because formatting above is not so good

[protonLeah]

Please Login or Register  to view this content.

[Mile029]

Please Login or Register  to view this content.

awesome, works in my spreadsheet, but when I put it in a macro, it doesn't, gives me a VALUE error. is there something I'm missing?

[FDibbins]

... but when I put it in a macro, it doesn't...

Then perhaps you should have specified that it was going into a macro?

[Mile029]

Then perhaps you should have specified that it was going into a macro?

Sorry, it's going to be in a macro.

I've used SUMIF in macros before and they basically work the same.

This is my line in the macro:
Const F = "IF({1},ROUND(SUMPRODUCT(--(left(E:E#,9)=left(E#,9)),L:L)),2)=0)"

basically looking to see if it totals to zero.

[AliGW]

Thread moved to the correct section and title updated.

[FDibbins]

Thanks Ali, I should have done that, too