Sumif if the number starts with.......
Upcoming Power Excel Seminars
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!!!!
    Russell

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

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


  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif if the number starts with.......

    Quote Originally Posted by Aladin Akyurek View Post
    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 ]
    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. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,926
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Apr 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif if the number starts with.......

    Quote Originally Posted by JackDanIce View Post
    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. #9
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,495
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    New Member
    Join Date
    Apr 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif if the number starts with.......

      
    Quote Originally Posted by JackDanIce View Post
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com