Sumif(Left( Function

[potato.king]

I need to do a sumif based on the first 3 numbers for both the range and the criteria. The 1st 3 numbers in the range column (Column A), the 1st 3 numbers in B1. The easiest way would be to create a whole new column and run a =Left(A1,3) and copy that down and make that my new "range" column. I hate adding columns for functions. Any help would be greatly appreciated. Thanks again in advance! Here is what I'm trying to do:

sumif(Left(A1:A100,3),Left(B1,3),C1:C100)

 

[AlphaFrog]

This may work depending on your data.

=SUMIF(A1:A100,Left(B1,3)&"*",C1:C100)

 

[potato.king]

Thanks for help. Sadly, it's not working for my data. Column A has account #'s 52510, 52520, 52530, 65050. B1 would have 52500. Ultimately, it would add all the accounts with the prefix of 525. I don't know why the formula wouldn't work...it looks like it should. Thanks.

 

[AlphaFrog]

This should work for numeric 5 digit values.

If you have Excel 2007 or later...

=SUMIFS(C1:C100, A1:A100,">="&Left(B1,3)*100, A1:A100,"<"&(Left(B1,3)+1)*100)

If you have Excel 2003 or earlier

=SUMPRODUCT(C1:C100, --(A1:A100>=Left(B1,3)*100), --(A1:A100<(Left(B1,3)+1)*100))
or
=SUMIF(A1:A100,">="&Left(B1,3)*100, C1:C100) - SUMIF(A1:A100,">="&(Left(B1,3)+1)*100, C1:C100)

 

[potato.king]

You are a genius! It works wonderful. Just as I hoped! Thanks again for sharing your mad talents and skills!!!!!

 

[vahiddastitash]

Help me . below formula doesn't work .
=SUMIF(left(All!D:D;4);Total!C2;All!G:G)