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.

You are most welcome.

Modifying the raw data sheet/table to use checkboxes wouldn’t be too difficult. (You could make a copy of the existing spreadsheet/document and make the modifications in the copy so as not corrupt your original data.) But I would understand if you wanted to stop modifying your spreadsheet at this time. You could revisit this option later, perhaps when you have the next round of voting.

To give you an idea of what is involved, the goal being to substitute the “X” for a checkbox in both the raw data sheet and the summary sheet:

• Make copy of spreadsheet.
• Add new column in the raw data sheet that will contain the checkboxes.
• In the first row in that new column (let’s assume this is column C, so cell C2, and that the column containing the “X” is column B), enter the formula: IF(B2=“X”,true,false). (See example below.)
• Copy the formula and paste for all remaining rows. You will see that the cells contain the value TRUE when there is an “X” in column B, and the value FALSE when there isn’t an “X”. Using a function to convert the “X” to TRUE/FALSE is more convenient than doing it manually and prevents errors:

• You don’t want to keep using this function because you will want to delete column B eventually because you won’t need it if you are using checkboxes. (And if you delete column B right away, the formula in column C will become undefined as there is no longer a column B.) So copy cells C2 through C13 (in my example; you need to select all the rows in your table, through row 681), select cell C2, right-click the cell and choose Paste > Paste Values. (Do not choose Paste > Paste Formulas as you only want to use the actual values.)
• Select all cells in column C (my example) and choose the Checkbox format :

• Now you are going to modify sheet “Street Vote %” to use the checkboxes. In sheet “Street Vote %”, select cell B2, double-click the cell to enter edit mode and click on the 3rd argument of the COUNTIFS (plural) function, the one that refers to the cells in the raw data sheet containing an “X”. (In my example it is the range Sheet1::Table 1::B2:B13).
• Go to the raw data sheet and select the rows containing the checkboxes:

• Remove the “=X” in the COUNTIFS (plural) function and replace it with the word “true” (without quotes):

• Copy cell B2 and paste it in cells B3 through B5 (B38 in your case), choosing to paste formulas. (You really want a dynamic calculation here.)
• Finally, go to the raw data sheet and delete the column containing the “X” as it is no longer needed now that your percentage formula uses the checkbox column.

(COUNTIF(,)E2:E17,=X)÷E2:E17). 6X6, this is a copy of the formula that I am applying to the cell where I want the answer, which is now formatted at a percentage. I am getting a syntax error and I am unable to discover the error.

Advise please, and I sincerely appreciate your help!

This is an example from the spreadsheet I am working to help you visualize what I want. Answer will be put in I17 and that cell is formatted as percentage.

Well, it will not let me post a copy of the sheet I'm working with.

E is the X voted column and there is 16 lines for the street name Barrinson begging on E2. The answer is put in the far right column I at line 17. Not shown is column A. I have used by typing =COUNTIF(E2:E17,"X")/COUNTA(E2:E17) AND GET A SYNTAX ERROR which I am unable to determine the error. I17 is formatted for percentage.

I'm struggling and don't know why. If I don't respond it's because it is late here and will pick the back up tomorrow.

I really appreciate your patience.

6X6, thank you for what you're doing to help me. I have attached two photos of my screen showing the formula as typed and the error message at the bottom left. I am stumped! What is frustrating is that I know the answer is simple!

[Edited by Moderator]

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:

Now, a new question please. I attempted to use the same formula, just changing the cell numbers, for a street called Borg, see attachment. I do not get a number of 300, nothing at all, for the three X'd boxes in row E. The formula is stopped where I should have a percentage of 300 shown before correcting to ROWS.

And, would it be possible to copy the formula from a working cell, paste it where I want it and correct the cell numbers into the new location?

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.

I am spending some time catching up other details on this vote spreadsheet and I really do enjoy working with this now. I want to spend a couple of days cleaning this up and then I would certainly take you up on tables. I am aware of tables but not for their purpose.

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.

Good example and I will be back with you to dive into the tables. This spreadsheet I am working with is active right now in a voting process of an HOA until the 22nd. I developed it to aid with paper count ballots that are handed in over a 30 day period. Your stamina is exemplary, many with knowledge would have flipped me off over my lack of understanding. I now understand more than the day I asked the question.

Good afternoon 6X6, I have caught up my 680 line spreadsheet and if you are still interested, I would love to use a table and I know hardly anything about them. There are 15 street names that we use and we are wanting to see the percentage of voters from each street. All of the percentage formulas are in place and functioning correctly. This spreadsheet, when printed horizontally, is about 15 pages long.

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)?

We use this spreadsheet to collect who voted for a person running for a Board of Directors position. I set it up with in column A an owner, column B a street number, column C, street name, column D, date votes counted, column E, called voted where an X is placed, column F, voted but did not vote for anyone, just submitted a vote to help get to the 35% required to complete an election, column G and H hold the names of people running for election. I can expand to another column if additional people are running for election. And in this spreadsheet I added column I for the percentage of homes on each street that voted. I hope that it will highlight to the Board of Directors ways to improve the subdivisions that participate in elections.

As I mentioned, we have 680 homes that begin on row A2, and complete on row 681. Each street is in numerical order.

Summary is below with the total of votes received, the number of votes required and the remaining votes required to meet the 35% obligations.

If I understand your explanation, I believe the second option, multiple sheets makes the most sense. Please tell me if you disagree.

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.

6X6, a screenshot is attached: I am at the point where I am wanting to see if the street name Augusta in Street Vote % matches on sheet one and I am getting no reply. Interesting, at the bottom of the screen, it shows the formula result 16 which is correct. Sixteen is not shown in B2, have I done something wrong at this point?

Okay, I figured out that I had not hit a return and have now arrived where we need to turn this into a percentage. See my screenshot attached. I feel like I am accomplishing something =, of course with your help.

And this will repopulate each time we add additional votes, I assume?

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:

6x6, I think I did backspace or hit delete when I made a typo, darn it! Question: Can I hand-type the formula using the cells from my sheet and table, or do I need to start the whole process over from where you began guiding me?

6x6, when you say modify, are you meaning added more votes to the table? We are concluding the voting tomorrow and will be adding votes, as we did yesterday and again today. It is correctly counting the homes voting per street, except for B2.

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.

Thank you 6x6, the table is now correctly showing the percentages. The voting process is over and we have the correct numbers from ballots. I cannot thank you enough and I wish there was a way that I could repay you. I am a dangerous novice using Apples Numbers.

I wish I had known about the Check Mark when I was building this spreadsheet. I would certainly prefer using the check mark, my concern is having to rebuild the formulas. I used COUNTA to total each column that used the X. It would be easy enough for me to replace an x with a check mark and I know that the COUNTA would still accomplish the result correctly. it's what would be required to rebuild the table that has me concerned. I almost think it would be easier to delete the table and just rebuild it.

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

6x6, thank you, and your reference that cell is formatted as a percentage, does that mean all of column A or the cell where the answer is?

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.

No, you cannot post a file on this forum but you can post a screenshot (if the data is not personal) using the Image Insertion button in the toolbar of your reply:

You are almost there. You missed the = sign. It should read "=X", not "X".

6X6, Yes, now I have a formula that is working but, the answer displayed is 100%, not the actual percent of those that have an X as has voted. Suggestions?

You deserve a medal 6X6 - I can't thank you enough for your patience with me. I wish I had the Numbers intelligence that you have Sir. You are done with me.

It was a pleasure. I’m glad you got things working.

Thanks, Ian!

Okay, I copied each street name, (37) and hit return. The names did not populate and an error message was given. I have sent two photos that I hope define the error.

[Edited by Moderator]

Thank you 6X6, here is my screenshot and the street names are now correctly where they belong. You wanted me to let you know when I got to this point.

When I get to "Now copy cell A2, select cells B3 through B38 and paste the formula:" I am unable to copy A2, see the attached photo.

If you haven't yet modified cells B3 through B38 (to include the division), go to cell B3, copy the cell and paste it in B2. It will restore the COUNTIFS function as it was.