Any way to get a count of filtered rows?

Hi Brian,

While Numbers 3 doesn't support categories, it's still possible (fairly easy, actually) to get category totals or counts. You'll want to do this on a separate table.

Here's an example, using the same categories as above in column B, but with some data aded in column C. The data table is named "Data," the summary table, reporting the count and total for each category, in named Summary. For ease of in head calculations, the 'amount' for every entry in a category is the position of that letter in the alphabet, making the total count x 1 for a, count x 2 for b, etc.

Data has no formulas. All data shown (categories and amounts) can be considered as directly entered from the keyboard, or pasted in from another source.

Summary has two formulas, one to count the number of entries in each category, the other to total the amount entered in each category. The category list is entered directly in column A. Categories must be exact matches of those in column B of Data.

Summary::B2: =COUNTIF(Data :: $B,A)

English: Count all of the entries in Data, Column B that match the entry in this row of Column A of this table.

Fill this formula down from B2 (of Summary) to the last row of the table.

Summary::C2: =SUMIF(Data :: $B,A,Data :: $C)

English: Check the category data in column B of Data. If the category on a row matches the value in this row of column A of this table, include the value in that row of column C of Data in the total calculated on this row.

Fill this formula down from Summary::C2 to the last row of column C of this table.

NOTE that the calculations do not depend on the table being sorted into any particular order.

For another example, open a new document from the Personal Budget template included with Numbers, and examine the category totals calculations in that document.

Regards,

Barry

Hi Brian,

I want to restate, along with Barry, the usefulness of COUNTIF() AND COUNTIFS(). Whatever filters you are using should not be too difficult to recreate with these functions.

The other thing that Barry suggests is the use of summary tables. I have grown to love these. I can pull data into a summary table and have it already sorted. I might put a filter on it to hide unneeded rows.

quinn

Hi Brian,

I see that COUNTIF is recommended by Barry and quinn 🙂.

A table with a Header Row and a Footer row.

Column A was simply my attempt to see what a filter would show.

Formula in A2 (and Fill Down) =ROW()−1

Formula in Footer Cell A9 =MAX(A)

Suppose that one of the Filter rules is to show rows where column B equals a,

Formula in B2 (and Fill Down) =IF(B2=B$1,1,0)

Using B$1 makes it easy to change the formula, if that is required, by typing a new value.

Formula in Footer Cell B9

=COUNTIF(C,1)

Formula in C1 ="="&B1&"?"

Now apply the filter(s) (and hide column C if you wish)

That should work if you have a number of filters linked by AND, but not if they are linked by OR.

Regards,

Ian

Hi Brian,

I don't know if this is a glitch in the program but this may work for you. My column E has 9 entries. When the filter is off this is recognized by COUNTA. When I apply the filter I see the result below. I haven't been able to get a formula in the table that will reflect this so if you need to use the result in a formula you would need to use COUNTIF or COUNTIFS.

quinn

Hi Brian,

Have a play with this https://www.dropbox.com/s/isbcm5jkqhmn6i0/Dynamic%20Filter.numbers?dl=0

The Conditional Highlighting in Column B is not necessary for this to work. I added it for clarity.

Formula in C1 ="="&"'"&B$1&"'"&"?"

Formula in C2 (and Fill Down) =IF(B=B$1,"Yes","No")

Formula in Footer Cell A11 ="Body Rows matching "&"'"&B$1&"'"

("'" is double quote, single quote, double quote)

Formula in Footer Cell B11 =COUNTIF(B,B$1)

Now type (or Copy and Paste) a new value into B1

Filter Rule is this

Filtering by Column C (currently named by C1 ='a'?) but when you enter a new value in C1, Numbers magically changes the Filter rule to whatever is in C1

Now apply the Filter (Tick the Checkbox to the right of Filters)

With the Filter on, enter a new value in B1

Upper and Lower Case (b or B) do not matter in this example.

Cell Data Format is magically handled too:

Thank you for the question. Playing with it taught me lots about the charm of Numbers.

Regards,

Ian.

Hi Brian,

Still living in the past here, so what works below in Numbers '09 may or may not be available in Numbers 3.

Select the table, then click Reorganize (in Numbers '09, a button in the bar above the workspace). Set up automatic categories on one column (B in this case) using the Reorganize dialogue:

Sort the table on the column containing the categories to lines from each category together.

Numbers will insert category row for each chnge in the content of column B.

Click on the cell in column B in any of the new Category rows, then click on the small triangle that appears at the right side of the cell to view the contextual menu.

Choose an appropriate item to display in these rows (and in this column). Since the data in the column is all text, I chose "Count. Results can be seeen in the category rows for categories d, e, f, and g, which are neither off-screen nor covered by the menu.

The disclosure triangle in the Column A category rows allows you to hide the data rows forthat category. The count (or other calculartion continues to show in column B).

Regards,

Barry