Sumif if the number starts with.......

[ermccarthy]

I need a sumif formula that will calculate everything that starts with a certain 3 numbers.......

If I have a range of A1:B:200.
I want it to look for everything in Column A that starts with 110 and sum column B. I was trying something like...
=sumif(left(A1:B200,3),110,B1:B200)......I know how to write this formula if the search was for 110, but with something starting with 110, I'm Stumped!!

PS: I am not able to sort the data feild at all.

Any help would be greatly appricated!!!!

[Aladin Akyurek]

On 2002-02-27 13:59, ermccarthy wrote:
I need a sumif formula that will calculate everything that starts with a certain 3 numbers.......

If I have a range of A1:B:200.
I want it to look for everything in Column A that starts with 110 and sum column B. I was trying something like...
=sumif(left(A1:B200,3),110,B1:B200)......I know how to write this formula if the search was for 110, but with something starting with 110, I'm Stumped!!

PS: I am not able to sort the data feild at all.

Any help would be greatly appricated!!!!

SUMIF cannot handle such a complicated condition.

Try:

=SUMPRODUCT((LEFT(A1:A200,3)+0=110)*(B1:B200))

As Chris has pointed out, the ranges must be equal.

Aladin

Special Note for Jack: LEFT makes the numbers all 3-digit text values, +0 makes them 3-digit numbers which then can be compared with the number 110. Voila an example use of +0.

[ This Message was edited by: Aladin Akyurek on 2002-02-27 15:08 ]

[Chris Davison]

On 2002-02-27 14:07, Aladin Akyurek wrote:

SUMIF cannot handle such a complicated condition.

Try:

=sumif(left(A1:B200,3),110,B1:B200

=SUMPRODUCT((LEFT(A1:A200,3)+0=110)*(B1:B100))

Aladin

Special Note for Jack: LEFT makes the numbers all 3-digit text values, +0 makes them 3-digit numbers which then can be compared with the number 110. Voila an example use of +0.

Aladin, those ranges don't look equal... is that okay ?

since I used your sumproduct revelation, I've always made sure they are the same sizes, is that only for array formulae ?

thanks
Chris

[Aladin Akyurek]

On 2002-02-27 14:32, Chris Davison wrote:

Aladin, those ranges don't look equal... is that okay ?

since I used your sumproduct revelation, I've always made sure they are the same sizes, is that only for array formulae ?

thanks
Chris
[img]/board/images/smiles/icon_smile.gif[/img]

No, it's not OK. They must be equal:

=SUMPRODUCT((LEFT(A1:A200,3)+0=110)*(B1:B200))

Thanks.

Aladin

I'm gonna edit my reply, so be warned