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

I prefer the 2nd option as well. This way you will be able to access the summary sheet/table more quickly, without having to scroll back and forth.

Could you clarify: "Summary is below with the total of votes received, the number of votes required and the remaining votes required to meet the 35% obligations."? I thought we were only looking at percentages of votes but we can certainly add more summarized data in the new summary sheet. Let's focus on that part later.

First, let's create a new sheet. Click the "+" icon to the left of "Sheet 1" (or your leftmost sheet). Double-click the name of the new sheet to enter edit mode and rename the sheet, e.g. Summary. The new sheet will show a default table. We'll use it to display the percentages.

How many unique street names do you have? I'd venture about 70 or so. If you have 70 unique street names, you'll need to have at least 71 rows in the new table (the first row will be used for column titles). So add that many rows by either clicking several times the "=" icon at the bottom left of the table. A quicker way would be to copy 10 empty rows, to select the 1st cell of the last row and paste (repeat by selecting the leftmost cell of the last row.)

The new part will be to import the unique streets names. For that, in the new table of the Summary sheet, select cell A2 and type "=UNIQUE" (without the quotes) and press the tab key (above the caps lock key). You will see the following:

Click on the word "array", select your raw data sheet (the one with 680 rows) and select all the values in column C (the street names). (Don't select more cells than the ones containing street name values.) Press return and you should see that the function has imported all the unique street names to the table in the Summary sheet. Using my raw data example above, this looks like this (ignore Sheet 1 through Sheet 4):

Let me know when you get there. The next step will be to use the COUNTIFS function in the new table.

(As a side note, if the only statistics based on street name are the percentages, you can delete columns C through E as you won't need them.)

My instructions were not clear. The UNIQUE function takes a range of cells as the 1st argument (ignore the other arguments “unique-by” and “occurrence”). You need to select the range C2:C681 in one swoop (I’m assuming that the street data starts in cell C2), not select the individual cells. This is the beauty of the UNIQUE function: it finds for you the unique values in a range of cells, without your having to hunt for them.

Using my example with 12 rows of raw data (A2:A13, corresponding to your C2:C681 range), here is the formula (bottom of the screenshot):

Addendum: A range of cells is all the cells between the lower and upper bounds. So the range A2:A13 refers to the collection of cells A2, A3, A4…A11, A12, A13.

Great! The next step will be to add the % formula in column B, in the sheet “Street Vote %”.

This will be based on the COUNTIFS (plural) function that can count rows on multiple criteria (whereas COUNTIF can only use one criteria).

To make it easier, I modified my example to match your sheet names (“Sheet 1” for the raw data, “Street Vote %” for the summary). In both sheets the data is in Table 1 (each sheet has its own Table 1).

In sheet “Street Vote %”, select cell B2 and type “=COUNTIF” (without the quotes) and choose the COUNTIFS (plural) in the suggestion bar to see (the formula bar is at the bottom of the screenshot):

Now, to select the data, click “test-values”, click Sheet 1 to select the sheet and select the range C2:C681 (just like you did for the UNIQUE function):

(My own street data is in column A.)

Now, click “condition”, go to sheet “Street Votes %” and type the following:

“=“ & A2

Press return and see if the resulting number matches the number of times street “Augusta” appears in Sheet 1. (In my example, street “Alan” only appears 3 times.) If the number matches, proceed to the next step.

(The “&” symbol is the concatenation operator; it joins characters into a single string. Numbers translate “=“ & A2 into “=Augusta”. This is similar to the “=X” you used before.)

Now double click cell B2 to enter edit mode, place the cursor right before the right parenthesis (the “crescent” icon), type a comma and you should see a new “test-values” field. Click it, click Sheet 1 to select the sheet and select the range E2:E681 (the values where the “X” appears):

Place the cursor right before the right parenthesis, type a comma and type the following (including the quotes):

”=X”

Press return to validate the formula. You should now see the number of times a vote was cast for street “Augusta”. (In my example, street “Alan” has 3 votes.)

To make sure everything looks good, copy cell B2, select cells B3 through B38 and paste. You will then see the number of votes for each street.

Let me know when you get there. The next step will be to divide the number of votes to get the percentage.

Great! Now let's calculate the percentage.

In Sheet "Street Vote %", double-click cell B2 to enter edit mode. Type the following:

=COUNTIF

And select the COUNTIF (singular) function in the suggestion bar (here you are going to count how many times a specific street appears in Sheet 1, the raw data sheet):

Click "test-array", go to Sheet 1 and select cells C2 through C681 (this is similar to what your did with the COUNTIFS (plural) function).

Go to Sheet "Street Vote, click "condition" in the second argument of the COUNTIF (singular) function and type (including the quotes):

"=" & A2

Press return to validate the function:

Now copy cell A2, select cells B3 through B38 and paste the formula:

Finally, select Column B and format it as a percentage:

Let me know when you get there. The next step, which is really optional, would be to use checkboxes instead of entering an "X" in the raw data table. That would make data entry easier. Let me know if you are interested.

You are in edit mode (the function window is still open). Press return to validate the function and see the result of the calculation in cell B2.

Something looks wrong in the formula window: it only shows the COUNTIF (singular) function. The COUNTIFS (plural) function is missing. You might have inadvertently deleted it.

The full calculation should look like this:

The last step of adding the COUNTIFS (plural) function (not the steps to add the division to calculate the percentage) was to copy cell B2 and paste it in cells B3 through B38. So cells B3 through B38 contain the right call to the COUNTIFS (plural) function. So you can copy cell B3 and paste it in B2 to restore what was in B2 (which was unfortunately truncated when you tried to add the percentage calculation).

After restoring cell B2, modify it by adding the division so that it calculates the percentage. (Refer to the procedure in my 10/21 reply.)

All the formulas are completely dynamic. So you can keep on inserting “X” in your raw data sheet. The “Street Vote %” sheet will automatically reflect the added data without your having to do anything.

Let me know if anything is ambiguous.

Here is an example showing the formula (at the bottom) and the fact that the cell is formatted as a percentage:

Just the cell containing the formula. You can also customize the number of decimals by typing "0" so that the percentage is rounded to an integer, or by typing "1" to have only one decimal.