Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: SUMIF Using Greater/Less Than Symbols In Text Criteria

  1. #1
    Join Date
    20th October 2008
    Posts
    2

    SUMIF Using Greater/Less Than Symbols In Text Criteria

    Hi,

    I'm trying to get a simple Sumif formula to work. I have used this formula a hundred times before but this time the results are inaccurate. I suspect that it may be because of the math symbols which are used in the criteria text i.e. < and >

    My formula is as follows:

    =SUMIF('Raw Data Export'!D:D,"< No Project >",'Raw Data Export'!G:G)

    A data extract from the sheet named "Raw Data Export" is attached.

    Regards,

    Leanne
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; October 21st, 2008 at 08:48.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th February 2004
    Location
    Florida
    Posts
    181

    Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

    Hello,

    Not sure why the math symbols are not recognized. How about as a work around, use the asterisk as a wildcard.

    i.e
    SUMIF( 'Raw Data Export'!D:D,"* No Project *",'Raw Data Export'!G:G)

    HTH


    JL

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    20th October 2008
    Posts
    2

    Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

    The workaround works for me. Thanks, I didn't know wild cards could be used. It is weird why the original formula wouldn't work though.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

    Not sure why the math symbols are not recognized
    I reckon the < is interpreted as less than, as in =COUNTIF(someRange, "<0"). You'd think the tilde (~) would escape it, but it doesn't.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  5. #5
    Join Date
    19th January 2005
    Location
    Michigan, USA
    Posts
    844

    Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

    Thought maybe this would work (found from an answer by SHG elsewhere), but it doesn't:
    =SUMIF(D:D,CHAR(139) & " No Project " & CHAR(155),G:G)


    SUMPRODUCT will work though:
    =SUMPRODUCT(--(D2:D50="< No Project >"),G2:G50)

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

    Aha! =SUMIF(D:D, "=< No Project >", G:G )
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: SUMIF Using Greater/Less Than Symbols In Text Criteria

    Personally, I would Replace all < * > with ( * ) to save further problems you may not even know about.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Double Criteria & Greater Than Lookup
    By nzebrowski in forum HIRE HELP
    Replies: 4
    Last Post: February 4th, 2009, 01:15
  2. Sumif Criteria Based On Color Of Cell Or Text
    By MJohn1978 in forum EXCEL HELP
    Replies: 2
    Last Post: May 2nd, 2007, 21:59
  3. Sumif With 2 Criteria
    By BGOLANOWSKI@gma in forum EXCEL HELP
    Replies: 7
    Last Post: December 13th, 2006, 02:01
  4. Replies: 7
    Last Post: June 21st, 2006, 06:30
  5. sumif 2 or more criteria
    By mhoover01m in forum EXCEL HELP
    Replies: 17
    Last Post: January 11th, 2006, 09:31

Bookmarks

Posting Permissions

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