Making formulas work across multiple sheets in numbers

Discussion in 'iPad Help' started by Angy, Dec 3, 2014.

  1. Angy

    Angy
    Expand Collapse
    iPF Noob

    Joined:
    Dec 3, 2014
    Messages:
    1
    Thanks Received:
    0
    image.jpg image.jpg Hi folks. I'm a forum newbie so I hope I'm doing this correctly. I use my iPad for collecting field data for ecological research as well as recreation.
    Can anybody can help me please? I'm looking for a formula to do the following:
    Sum the values for each species of each survey method. Then for each species, take the highest value for any one survey method and place it in the multi totals sheet under the correct survey date, pond number and species category. Do not count larva. I'm only interested in adults and in the case of GCN, juveniles.
    That is probably as clear as a murky pond!
    If so let me know if you don't get it and will try to elucidate.
    Thanks
     
    #1 Angy, Dec 3, 2014
    Last edited: Dec 3, 2014
  2. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend II

    Joined:
    Jan 8, 2011
    Messages:
    20,105
    Thanks Received:
    5,436
    You are probably looking for the SUMIF and/or the SUMIFS functions.

    SUMIF adds all items in a column/row that match the condition in another column/row. For instance, if you matched Frog in the Species row of your second table you could sum just those cells in the Survey 1 column that are in the same row.

    SUMIFS does the same thing, but lets you use multiple conditions. So if you had two columns, say one for species and one for development, you could sum only entries that match both Frog and Adult.

    There are several other xxxIF and xxxIFS functions which might be handy, like counting, averages, etc. They are in the Statistical category.

    When things get too complicated I sometimes create in-between tables or column, where I can generate values that can't be computed directly within the formula. For instance, when trying to match a condition you can not use a formula. It either has to be a simple condition like "> 0", or a cell. So if you need to match a computed variable, you pretty much have to create a formula in a cell, then match that cell in the IF expression.

    I hope that made sense. Like you guessed, what exactly you are trying to do is a bit murky to me, so this general advice is all I can offer.

    Good luck.
     
    • Informative Informative x 1
    • List
  3. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend II

    Joined:
    Jan 8, 2011
    Messages:
    20,105
    Thanks Received:
    5,436
    If you create a Spreadsheet using the Personal Budget template you can see a simple example of the SUMIF function being used in the Actual column.
     

Share This Page