Summing/counting checked checkboxes?

Hi Quantum,

The COUNTIF function will count the number of occurences of a condition within a range. For example,

=COUNTIF(C2:C5,"=TRUE")

Will count the number of checked tickboxes in the range C2 to C5 (on one table).

To create an album in Numbers, you could have a table for each 'page'.

A summary table could contain a formula to count the number of ticked check boxes over every table. Numbers can reference cells on different tables and sheets. However, I can see this could get quite involved!

The dilemma is that Numbers is not really designed to be an album, and Pages does not allow checkboxes.

Perhaps you could create your album in Pages, and keep a record of ticked pictures in Numbers.

Regards,

Ian.

Hi Q,

There's no place in that formula where you should be 'selecting' A1 on the table on Sheet 1. The formula builds the reference to that cell using the contents of column A of the Summary table plus the text typed into the formula.

The formula in B is =INDIRECT(A&"::Table 1::A1")

"The formula in B is" means that every cell in column B of the summary table contains this formula.

Inside the parentheses,

"A" means 'the contents of the cell in column A (of this table) in the same row as this formula'

"&" is the concatenation operator, which joins the contents of "A" and the text that follows into a single text string.

The rest is a text string that provides the fixed part of the cell addresses from which the data is to be retrieved.

Together, those parts, in the formula in cell B2, construct the text string "Sheet 1::Table 1::A1"

INDIRECT reads that text string, translates it into a cell reference, and returns the value in cell A1 of Table 1 on Sheet 1 (TRUE, as the cell's checkbox is checked).

Regards,

Barry

Hi Quantum,

Update. This might do what you want in Numbers. It is not an album, and has several photos in a table.

Table 1 has your photos and checkboxes.

Table 1

and so on...

Table 2 uses COUNTIF to count the ticks on Table 1. Cell B1 of Table 2 contains:

=COUNTIF(Table 1 :: A1:D44,"=TRUE")

(Set the range to include the whole table.)

Table 2

Perhaps you could have other sheets with this design of 2 tables, one sheet per theme?

Regards,

Ian.

The problem you will have with trying to count up the checkboxes from a multitude of tables is that Numbers isn't designed to do that. You can easily count things in a single table but there is no cell referencing that works across tables (with the exception of a few functions but that feature is undocumented). One way to work around it is to gather the results from those checkboxes into a single table. This is pretty easy if your sheet (and/or table) names follow a set pattern such as Sheet 1, Sheet 2, Sheet 3, etc.. It is not very convenient if your sheet/table names don't follow a pattern.

Here is a sample sheet with a photo in a rectangle (shape) and a single-celled table for a checkbox. Sheet 2 looks identical except it uses a different picture. In fact, all I did was duplicate this sheet, change the sheet name to "Sheet 2" and drag a different picture into it. The picture is the "fill" for the shape. You can drag another picture into the shape to replace what is there or you can set the fill to a color if there is no picture.

And here is the summary sheet which includes those two sheets and some sheets that have not been created yet (thus causing the error triangles). It has a footer row for the total:

The sheet names are text. You type in the first two then drag down on them to fill in the column.

The formula in B is =INDIRECT(A&"::Table 1::A1")

The formula in the footer row is =COUNTIF(B,TRUE)

As you can see, if the sheet names are sequential, it is pretty easy to do. If the sheet names were something else, like "Parrot", "Pelican", "Robin", it would be difficult and error prone and, if you later change a sheet name in the sidebar you would have to remember to change the sheet name in the summary table too.

As Barry said, the "::Table 1::A1" in the formula is not a cell reference, it is a string of text that you need to type in. What I would do is copy/paste the formula from my post above. I included it in the text of the post, not just in the picture, so that you could copy/paste it.