I'm trying to get a row count for filtered results. On the desktop version of Numbers, Apple has a CountA formula built in to the bottom results bar (not sure what it's really called). But, if I create a CountA formula for the same column, it counts ALL rows, not just the visible ones. Why does Apple's behave differently, and how can I create the same formula they are using in my own field?
Jonathan Christensen wrote:
The problem with it is…I am entering the data twice. Once for the table filter, and again in the secondary table.
You can have avoid having to enter the filter year and then manually reset the filter on the table by adding a filter column something like this.
Change the year to 1973 and you automatically get this:
The formula in row 2 of the filter column, filled down, assuming the dates are in column F, is:
=IF(OR(FILTER YEAR::$A$2="ALL",COUNTMATCHES(F2,FILTER YEAR::$A$2)>0),TRUE,FALSE)
The formula in the "count" cell is simply =COUNTIF(Table 1::K,TRUE) where column K is the filter column.
The filter can be set like this (after clicking somewhere in Table 1):
When you want to show all the entries you can "turn off" the filter switch in the panel at the right or you can simply enter ALL or all for the year.
Once you've got the filter working you can hide the filter column.
SG
Jonathan Christensen wrote:
The problem with it is…I am entering the data twice. Once for the table filter, and again in the secondary table.
You can have avoid having to enter the filter year and then manually reset the filter on the table by adding a filter column something like this.
Change the year to 1973 and you automatically get this:
The formula in row 2 of the filter column, filled down, assuming the dates are in column F, is:
=IF(OR(FILTER YEAR::$A$2="ALL",COUNTMATCHES(F2,FILTER YEAR::$A$2)>0),TRUE,FALSE)
The formula in the "count" cell is simply =COUNTIF(Table 1::K,TRUE) where column K is the filter column.
The filter can be set like this (after clicking somewhere in Table 1):
When you want to show all the entries you can "turn off" the filter switch in the panel at the right or you can simply enter ALL or all for the year.
Once you've got the filter working you can hide the filter column.
SG
This is what the Apple Counta is doing on a filtered column (filter for text contains 1971). In my case, it is somehow sub grouping everything by the artist name (column B)…and then counting. Notice how they are grouped together. This is not part of CountA that I’m aware of. Is it?
If you drag the results from the COUNTA at the bottom and drop it on a footer cell in your table, you will see the formula it is using. It will create that formula in that cell in your table. Take a look at it and you will see how it is different from the formula you are creating.
A usual way to count the visible rows from a filter is to use COUNTIF or COUNTIFS. You provide the function the same condition(s) you used for the filter.
Ok…I dragged the formula to a cell, and honestly…I really cannot tell what it is doing. And if I change the filter, the formula in the cell doesn’t update anyway. Basically I have a huge database of a record library. I filter to see all releases from 1972 for example…but sometimes box sets are entered with multiple years, such as 1970, 1972, 1975, 1980. So, I need to search for “contains 1972” in column G and give me the total number of rows after the filter of contains 1972 is applied to the column.
Currently, I have it working by creating a separate table titled “FILTER YEAR” with a single field, where I type the year I am looking for (1972)…and then in my main table, in the footer of column G, I have this formula: COUNTIF(ORIGINAL RELEASE,"*"&FILTER YEAR::A$2&"*"). This gives the correct result. The problem with it is…I am entering the data twice. Once for the table filter, and again in the secondary table.
Upon seeing Apple’s CountA formula magically updating no matter what the filter…I wanted to try and recreate it. It doesn’t seem as if the nomenclature of “CountA” is correct. Is it? I can’t replicate what it’s doing with CountA.
Thanks! Yep that works.
I’d still like to figure out what Apple is doing with their formula sometime. It’s driving me nuts now.
CountA for filtered results
