macosnumbers
I am on 2014 Macbook Pro running Mavericks.
I have two tables.
In table 1, I have a cell that should store the number of cells in column A of table 2.
In the same table 1, I have a different cell that stores the number of cells in column E of table 2 that matches the text "MATCHED".
How do I write the formula for each case?
Thanks to your sample file, I think I was able to do what you wished.
Using an additional column to bring in the numbers corresponding to the codes (16AA, 1; 16AE, 2...) sorting by that column then hiding it the desired result was accomplished. To refresh the sort after adding data, unhide the column and sort again.
Here are the steps:
Create new column to the left of the "Code" column.
Add a formula to extract the proper number for each code and copy down.
Formula: =INDEX (Group::c, MATCH (B, Group::A))
(Using INDEX(MATCH) is a good way to get column data with out worrying about extra columns being added as is the case with VLOOKUP.)
Result:
In order to move the formula you have to drag the yellow circle on the bottom of the cell:
Best Answer
The great thing about numbers is they offer a great how-to guide. If you don't know how to do something then all you have to is start typing a formula and it will begin to auto populate, click on it and the functions sidebar will open with the function. it will give you examples, what the parameters are etc.
That's just a tip, but your lucky I'm here:
Since you didn't specify what you want the cell of Table 1 to count in the column of table 2 I just counted all non-blank cells. You can alter with whatever text based of the
countiffunction in the second example that compares against a string:1 : Table 1 Cell that stores number of cells in column A of Table 2. As described since you didn't specific I just counted all non-blank cells :
2 : Table 1 Cell that stores number of cells in Column E of Table 2 that matches text "MATCHED" :
Instead of typing you can just do this:
Type into the cell = then type COUNTIF then click on the column header (E) it will select the entire column, then click on condition and type "MATCHED" then press enter! Your outcome will be based off this :