(Numbers) COUNTIF values from one column match another column?

Solution 1:

Unfortunately Numbers doesn't support all the functions etc that Excel does, so using a SUMPRODUCT to do this in the same way you would in Excel isn't going to work.

I was hoping someone else may offer you a more elegant solution than the ones I offer here (I much prefer MS Excel and aren't very familiar with using Numbers). However, since you don't have another answer yet, I'll offer a couple of not so elegant ways for you to achieve what you want.

Method 1: Count the number of highlighted cells

Since you're using Conditional Highlighting (as opposed to having just manually shaded your cells), you can actually use a formula to count how many of them are shaded.

Unfortunately, I can't just now think of a way to do this while you have both sets of shading, so you'd need to remove the Conditional Highlighting for the wrong results (i.e. have no red shading).

Once you've done that, then:

1. Add a row at the end of your data
2. At the bottom of Column C (i.e. your Random Forest column) enter the formula: =COUNTIF(C2:C29,TRUE) (Note: Change C29 in this example to reflect the last row of Column C containing data.)
3. Now you should have a numerical value displayed to indicate how many cells in Column C were shaded with your Conditional Highlight
4. Repeat Step 2 for the bottom of each of your Columns by adapting the formula as necessary

As you can see, the above process counts the number of correct values in your data. Of course, once you have these values, you can then go ahead to add another row to calculate your percentages.

Method 2: Use additional columns to do the grunge work

Another way to do what you want is to first use another column to compare the values between the actual and predicted values and then to count the number of instances you get a TRUE result.

So, for example, you could:

1. Label Column L as Random Forest Check and at cell L2 enter the formula: =C2=B2
2. Based on your question you should get the result TRUE in that cell
3. Now copy the formula down Column L so that each cell displays either a TRUE or FALSE value
4. Add a row at the end of your data
5. Now at the bottom of Column C (i.e. your Random Forest column) enter the formula: =COUNTIF(L2:L29,"TRUE") (Note: Change L29 in this example to reflect the last row of Column L containing data.)
6. Now you should have a numerical value displayed to indicate how many times the value TRUE appears in Column L.

As you can see, by using the above process you will be able to count the number of correct values for your Random Forest column. You would then need to repeat the same process for each of your columns (e.g. use Column M for checking your Neural Network results, and so on). Then, once you have your values, you can then go ahead to add another row to calculate your percentages.

Finally, if you wanted to hide the extra columns, you can just go ahead and do that. Doing so will not prevent the COUNTIF formulas from calculating.