Formula to calculate percentage of cells with 'X' in Numbers for Mac

The parentheses are wrong but it's not really your fault as Numbers will "help" you by introducing its own parentheses when you select a function. (They are the little "crescent" icons you see at the end of your formula above.)

So, let's simplify the formula entry by just specifying the COUNTIF function. In the cell, start typing =COUNTIF. Numbers will then show you this:

Click the green COUNTIF suggestion in green to see this:

Click "test-array" and then select the cells E2 to E17 (my example uses A2 to A17 but it is absolutely the same):

Click "condition" and type "=X", including the quotes:

Press the return key to validate the formula:

In my example, 3 rows contain the value "X", so the function return value is 3, which is displayed as 300%.

If everything looks good, double click the cell (to enter edit mode), type "/COUNT" (without the quotes) and select COUNTA in the suggestion list:

You should see:

Click "value" and select the cells E2 to E17 (my example still uses A2 to A17), you can also type "E2:17" (without the quotes) as selecting the cells can be tricky at this point:

Press the return key to validate the formula:

Not a problem.

If you type the formula yourself, clear the cell and type this:

=COUNTIF(E2:E17,"=X")/COUNTA(E2:E17)

Note the quotes around =X, i.e. "=X". You also missed the COUNTA() function. You could replace the COUNTA() function by the value 16 if you are sure the number of cells will never change, e.g.:

=COUNTIF(E2:E17,"=X")/16

Finally, in this particular case, there is no need to surround the whole calculation with parentheses.

I hope this helps.

I think I know why: the COUNTA function only counts the cells containing a value. I see in your previous screenshots that the spreadsheet contains blank cells.

Replace the COUNTA function with the ROWS function. The ROWS function returns the numbers of rows regardless of the content of the range.

Here is the same example, this time using the ROWS function, with some cell values cleared:

Yes, copying the cell (just copy the cell itself – not the formula inside the cell – just like you would copy a cell value) where the formula works and pasting it to a new cell (for "Borg", for instance) would be the way to go.

After pasting the cell, double-click the cell to enter edit mode (you will see that the cell references have changed), click on the cell reference in the COUNTIF function and select the appropriate cells. Do the same for the ROWS function.

There is a more advanced way of counting cells using multiple criteria (using the COUNTIFS function – plural – instead of the COUNTIF function – singular) but if your spreadsheet is rather static when it comes to the streets, it would make things more complicated at this time.

Looking at your data, you may want to consider using a separate table to summarize your data. It would make things clearer as you wouldn't have to scroll down as much to see the percentages. This is also where the COUNTIFS (plural) function really shines. I could show you an example if you are interested.

To give you a foretaste, this would look like this:

You'll also notice that I'm using the Checkbox cell format in the raw data table, which makes it convenient if you are only trying to have a Yes/No or Done/Not Done type of data.

Hi 6x6,

I have been following this thread with interest. I admire your stamina, your suggestion to replace X with Tick Boxes and your suggestion to create a summary table to sum each street and the ticked boxes with SUMIFS.

Bravo! An upvote for your effort.

Regards,

Ian.

Hello Stephen,

I'd be glad to help. I guess the most important thing to know is that, as opposed to Microsoft Excel, you can have multiple tables in the same sheet (e.g. Sheet 1, which is the default sheet if you don't rename it) in Numbers. Also the terminology is important: the "spreadsheet" is the Numbers document/file, a "sheet" is a page within a spreadsheet, and a "table" is a table where you enter or calculate data. A spreadsheet can contains multiple sheets and a sheet can contain multiple tables (see below). (You can also add other elements such as charts.)

In your case, I suspect that you only have one spreadsheet, with a single sheet that contains a single table.

Here is the example I was referring to earlier, this time showing the sheet numbers at the top:

As you can see, I have 5 sheets in this spreadsheet: Sheet 1 thought Sheet 5. Sheet 5 is the one I showed you before. (The other sheets contain unrelated data; I use this spreadsheet as a scratch pad.)

Your current sheet probably only have one table but you could add other tables to the same sheet. Having multiple tables in the same sheet is convenient if you can see all of them at a glance. It becomes less so if a table contains a large number of columns, as your spreadsheet seemed to indicate. In that case, you have to scroll horizontally to see the other tables. Similarly, if your table has a lot of rows, especially for the raw data table (as opposed to the summary table that would contain summarized data, such as the percentages), you have to scroll a lot to enter the raw data and you have to scroll back up to see the summary table (you can also add tables beneath other tables; the position of the tables is not predefined; you can move them around at your convenience).

In light of the number of printed pages and the fact that you have a large number of rows and columns, I would recommend adding a sheet to summarize the data from the "main" sheet (the raw data sheet where you record your data). This way you could choose to print either the raw data sheet or the summary sheet, depending on the need.

Would you like to pursue the first option (multiple tables in the same sheet) or the second option (multiple sheets)?