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,683
    Thanks Received:
    6,580
    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,683
    Thanks Received:
    6,580
    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.
     
  4. WalkerMomma66

    WalkerMomma66
    Expand Collapse
    iPF Noob

    Joined:
    Mar 12, 2017
    Messages:
    3
    Thanks Received:
    0
    Let's say I want to create 10 sheets that hold expenses in various accounts. Then I enter a drop down menu that gives each expense a category in column D. I create sheet 11 where I enter these expense categories in column A(these will be found in the other 10 sheets in column D). Now I want to sum all 10 sheets by the amounts entered in Column F in each sheet to column B on sheet 11 if column A criteria is found in column D in the other 10 sheets.
     
  5. WalkerMomma66

    WalkerMomma66
    Expand Collapse
    iPF Noob

    Joined:
    Mar 12, 2017
    Messages:
    3
    Thanks Received:
    0
    To clarify, I can get sheet 1 to sum on sheet 11, it's adding the extra sheets to the function that has me buffaloed.
     
  6. WalkerMomma66

    WalkerMomma66
    Expand Collapse
    iPF Noob

    Joined:
    Mar 12, 2017
    Messages:
    3
    Thanks Received:
    0
    Example SUMIF(CYP9442::Table 1::$D:D,$A:A,CYP9442::Table 1::$F:F) But how to add my other sheets? :(
     
  7. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend II

    Joined:
    Jan 8, 2011
    Messages:
    20,683
    Thanks Received:
    6,580

    I'm not 100% sure what you are describing, but my guess is it should be the addition of the same formula for each table.

    (Formula that works on Table 1) + (same formular for Table 2) + . . . n number of tables

    This would result in a 'very' long forumula where a single mistake would break it, so alternatively you could could break it down by stages. Compute each sheet/table individually in a cell on the original sheet, or the final sheet, then sum the results. If you want to keep things neat, make all the intermediate calculaiton in a single column that you can hide once it's all working (or in an another table that you can move out of the normal view).
     

Share This Page