7 Replies Latest reply: Jul 14, 2013 7:10 AM by Yellowbox
kfrmac Level 1 Level 1 (0 points)

I have a column of values (dollar values) and I need to sum, but only those cell with values that are >$98 and <$169. How might I do this?


Numbers, OS X Mountain Lion (10.8.4)
  • 1. Re: Sum only the cells that are within a range of two values
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi kfrmac

     

    =SUMIFS(A2:A5,A2:A5,">98",A2:A5,"<169")

     

    Where A2:A5 is the range of cells. Insert your own range.

     

    Regards,

    Ian.

  • 2. Re: Sum only the cells that are within a range of two values
    kfrmac Level 1 Level 1 (0 points)

    Thank you, Yellowbox

  • 3. Re: Sum only the cells that are within a range of two values
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi kfrmac,

     

    Glad to help, and thanks for the greenie. The Function Browser in Numbers is handy for a quick overview of functions. The 'Numbers User Guide' and the 'Formulas and Functions Help' are handy too. Download from the Help Menu in Numbers.

     

    Regards,

    Ian.

  • 4. Re: Sum only the cells that are within a range of two values
    kfrmac Level 1 Level 1 (0 points)

    Hi Yellowbox, Would the same formula work with COUNTIFS on one column of text, and I need to say count only the cells with either "apple" or "orange" and NOT count any other fruit listed in that column?

  • 5. Re: Sum only the cells that are within a range of two values
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi kfrmac,

     

    Use COUNTIF for Apple and again for Orange, then sum.

     

    Screen Shot 2013-07-14 at 6.20.27 PM.png

     

    Formula in B10 is:

     

    =COUNTIF(B$2:B$9,"=Apple")

     

    Fill down to B11 and change to Orange:

     

    =COUNTIF(B$2:B$9,"=Orange")

     

    The $ symbols will keep the rows absolute as you fill the formula down.

     

    Formula in B12 is:

     

    =B10+B11

     

    Regards,

    Ian.

  • 6. Re: Sum only the cells that are within a range of two values
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    KF,

     

    Your question indicates a preference for the Excel style of spreadsheet design. By that I mean that you are not making use of features that are unique to Numbers, so not available in Excel. In Numbers we don't have to be explicit as to the row range because we can assign separate tables to data and the summary, or we can put the summary calculation in Footer Rows. With the Numbers style of layout, you could write:

     

    =COUNTIF(B, A)

     

    This expression, without any change for Apples or Oranges, could have been used in Ian's example if his summary had been in Footer Rows.

     

    The B in the first argument asks to look in column B's body rows, and the A says to match the content of Column A in the same row of the expression.

     

    Jerry

  • 7. Re: Sum only the cells that are within a range of two values
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Jerry,

     

    Thanks for your reply and insight. I certainly enjoy the unique features of Numbers (I remember you calling them the "charm" of Numbers - such a good expression!). One such unique feature of Numbers is to allow separate Tables on one Sheet (or different Sheets) - discrete blocks of data such as a Database and Summary Tables. And as you noted, another unique feature is the special (charming!) properties of Footer Rows [and Header Rows and Columns].

     

    Before I can make full use of the charm of Numbers, I need to know a bit more about the overall aim of of a project. KF's second question was:

     

    one column of text, and I need to say count only the cells with either "apple" or "orange"

     

    I agree that my 'Apple Orange' reply was Excel-like. Without knowing the overall aim of KF's document, I am having trouble understanding your solution:

     

    =COUNTIF(B, A)

     

    Thanks and regards,

    Ian.