I don't know if it's possible to do this with functions, but it might be. There is a function called VLOOKUP that seems like it would do what you want, but I couldn't get it to quite work in my experimenting with it—a function that exists within one cell has a hard time returning multiple values for multiple cells.
What I think does work, though perhaps not as convenient as you might want, is the filter view. On the right side of the window, if you select the view that says "Sort & Filter" and the Filter tab, you can "Add a Filter" that will match a value in a specified column. Toggling that filter using the checkbox in the corner of that Filter's box will hide all of the rows that don't match that criteria, then it's a simple matter of highlighting all of the cells, copying and pasting to another sheet.
Unfortunately, this won't continue to add rows as you add rows in the first sheet, so if that functionality is something you need, this won't answer your question. If you only need a one-time copy, though, I think that this is the easiest option (and the only one that I know of, but I'm not extremely familiar with Numbers).
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:
- Add a row at the end of your data
- 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.)
- Now you should have a numerical value displayed to indicate how many cells in Column C were shaded with your Conditional Highlight
- 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:
- Label Column L as Random Forest Check and at cell L2 enter the formula:
=C2=B2
- Based on your question you should get the result
TRUE in that cell
- Now copy the formula down Column L so that each cell displays either a TRUE or FALSE value
- Add a row at the end of your data
- 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.)
- 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.
Best Answer
Transpose the sheet (Table->Transpose rows and columns), sort, and then transpose back. However you may lose some of the formatting, like column widths and row heights.