Making formulas work across multiple sheets in numbers

[Angy]

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

 

[twerppoet]

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.

 

[twerppoet]

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.

 

[WalkerMomma66]

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.

 

[WalkerMomma66]

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.

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.

 

[WalkerMomma66]

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.

Example SUMIF(CYP9442::Table 1::$D,$A:A,CYP9442::Table 1::$F:F) But how to add my other sheets?

 

[twerppoet]

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.

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).