Counting the number of filled cells

Hi,

Unlike Excel, Numbers can't "see" the color or other formatting of cells to count them (unless you use a script, which can see the color).

But Numbers can count if it knows what caused the color or other formatting. For example, if you have Numbers turn a cell's background color green whenever you have the value TRUE in it (by for example checking a checkbox) you can easily do something like this.

The formula in A11, filled right, is:

=COUNTIF(A,TRUE)

Change that to =COUNTIF(A;TRUE) if your region uses , as a decimal separator.

In my example Row 11 is defined as a Footer Row so that it can hold the formulas referring to the entire column above. You can designate how many Footer Rows you want through the Table menu.

I got the cells to turn green by selecting them, choosing Checkbox in Cell > Data Format in the panel at the right, and then applying this Conditional Highlighting like this:

You may not want to use Checkboxes the way I did. If so, you can analyze what are the conditions where you want a cell to be green, and then adapt the COUNTIF (or COUNTIFS) accordingly.

SG

Hi glowstonetrees,

Adding to the excellent ideas from quinn and SG:

I used Conditional Highlighting to give green fill to blank cells.

Anything in a cell makes it not blank. For example, cell A6 contains an invisible character (a single space). That may suit your aim.

Now count the number of green cells (blank, no data) in each column.

Row 11 is a Footer Row.

Formula in A11 (and fill right) =ROW()−1−COUNTA(A)

ROW() is 11.

Subtract 1 to find the number of Body Rows.

The COUNTA function counts all cells in that column that contain any value, so subtract that to calculate the number of green cells in a column.

I add to quinn's and SG's requests for information on your overall aim.

Regards,

Ian.

Hi glowstonetrees,

Numbers does not have functions to interact with cell formatting. It is looking for data within the cell.

You could set up a table so that when you typed "G" into it it went green in text and background so it looked like the table above. Then you could count "G"s.

Sometimes asking the larger question that you are trying to solve will get you better help.

quinn