# Thread: Sumif if the number starts with.......

1. 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!!!!

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

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 ]

3. On 2002-02-27 14:07, Aladin Akyurek wrote:
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:

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

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

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

4. On 2002-02-27 14:32, Chris Davison wrote:
On 2002-02-27 14:07, Aladin Akyurek wrote:
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:

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

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

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

I'm gonna edit my reply, so be warned

5. ## Re: Sumif if the number starts with.......

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.

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 ]
I've tried to do the same formula but it didn't work for me. I have a excel sheet with +3000 values, what to sort out cost that are connected to different numbers (accounts). For example I'm trying to sum all cost connected to the "account" starting with number 1.

This is the formula I have tried:

=SUMIF((LEFT(F7:F3676;1)+0=1)*(E7:E3676))

Any ideas whats the problem in my formula?

6. ## Re: Sumif if the number starts with.......

This works if col A is text:

 A B C D E 1 108 1 8 D1: =SUMIF(A1:A20, "110*", B1:B20) 2 1080 1 3 10800 1 4 108000 1 5 1090 1 6 1100 1 7 1100 1 8 1100 1 9 1100 1 10 1100 1 11 11000 1 12 110000 1 13 110000 1 14 111 1 15 1110 1 16 111000 1 17 112000 1 18 113 1 19 1130 1 20 113000 1

7. ## Re: Sumif if the number starts with.......

Sanel, I believe your formula needs to be entered as an array formula CTRL+SHIFT+ENTER and not with just ENTER

8. ## Re: Sumif if the number starts with.......

Originally Posted by JackDanIce
Sanel, I believe your formula needs to be entered as an array formula CTRL+SHIFT+ENTER and not with just ENTER
Im getting closer now, used CTRL+SHIFT+ENTER as you said and got a "result" in the cell now. But it says "#NAME?" now in the cell. Maybe the +0 didn't work as it should?

9. ## Re: Sumif if the number starts with.......

This seems to work for a 'normally' entered formula:
Code:
`=SUMPRODUCT(--(VALUE(LEFT(\$A\$1:\$A\$200,3))=110),\$B\$1:\$B\$200)`
**EDIT**
Just noticed Aladin suggested similar but seemed to have missed out the double unary (--) to turn the TRUE,FALSE values to 1,0

10. ## Re: Sumif if the number starts with.......

Originally Posted by JackDanIce
This seems to work for a 'normally' entered formula:
Code:
`=SUMPRODUCT(--(VALUE(LEFT(\$A\$1:\$A\$200,3))=110),\$B\$1:\$B\$200)`
**EDIT**

Just noticed Aladin suggested similar but seemed to have missed out the double unary (--) to turn the TRUE,FALSE values to 1,0
Thanks for the help, it works now!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•