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

ermccarthy

Board Regular
Joined
Feb 15, 2002
Messages
224
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!!!!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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))

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
:)
 
Upvote 0
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))

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
/board/images/smiles/icon_smile.gif

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 :)
 
Upvote 0
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.

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

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?
 
Upvote 0
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​
 
Upvote 0
Sanel, I believe your formula needs to be entered as an array formula CTRL+SHIFT+ENTER and not with just ENTER
 
Upvote 0
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? :)
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top