+ Reply to Thread
Results 1 to 11 of 11

how do you put a "between" in a sumif

  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    how do you put a "between" in a sumif

    Hello. How do you write between 4300 and 4800 in a sumif. Is it this? <4300<=4800

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how do you put a "between" in a sumif

    "Between" is fairly ambiguous.

    When people say "between" what they really mean is greater than or equal to ___ and less than or equal to ___.

    Is that what you want?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: how do you put a "between" in a sumif

    yup equal to and less than or equal to.

  4. #4
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: how do you put a "between" in a sumif

    You can't.

    Best alternative: see http://www.excelforum.com/showthread...=1#post4149002

    Much in the same way you cant do

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how do you put a "between" in a sumif

    Quote Originally Posted by ammartino44 View Post
    yup equal to and less than or equal to.
    Like this...

    =SUMIFS(A:A,A:A,">=4300",A:A,"<=4800")

  6. #6
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: how do you put a "between" in a sumif

    Why can't I do this? =SUMIFS(June!G:G,June!D:D,">4300<=4800",June!E:E, "122002")

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how do you put a "between" in a sumif

    Do it like this...

    =SUMIFS(June!G:G,June!D:D,">4300",June!D:D,"<=4800",June!E:E,122002)

    Some moderator is going to "yell" at you!

  8. #8
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: how do you put a "between" in a sumif

    Why don't you have "" around the last criteria? That's not working. I've attached a spreadsheet.
    Attached Files Attached Files

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how do you put a "between" in a sumif

    A couple of things...

    In formulas, don't quote numbers as that turns them into TEXT values.

    In your file, the data in column A is TEXT. To convert it to numbers...

    Select the range A2:A269
    Goto the Data tab>Text to columns
    Click: Finish

    The formula is now working!

  10. #10
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Re: how do you put a "between" in a sumif

    Yeah I saw that it was text. But you have have quotes in the formulas that you recommended as well haha.

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how do you put a "between" in a sumif

    Yes, those expressions get evaluated as text strings:

    ">4300" and "<=4800"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. "sumif" not working with "today()"?
    By kghisla in forum Excel General
    Replies: 3
    Last Post: 11-08-2010, 10:39 AM
  4. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  5. Performing "SUMIF" where "range" & criteria" are texts
    By joseedua in forum Excel General
    Replies: 1
    Last Post: 02-11-2010, 01:09 PM
  6. Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  7. "Sumif" and "DropDown" in palm
    By Betointer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-03-2005, 11:02 AM
  8. [SOLVED] SUMIF(O18:O500,"=b21"Q18:Q5) i want to do this not "=001690"
    By steve alcock in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2005, 05:07 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1