requesting formulas for datapoints and percentages

I'm not sure if your last two columns are both representing scores. In any case, you can achieve what you're aiming for using some simple formulas like SUMIF, COUNTIF, and COUNTA.

I've randomly selected a few rows of data from your image and created four tables below: participant statistics by gender, participant statistics by grade, score statistics by gender, and score statistics by grade.

In Participants (gender), the main formulas used are COUNTA and COUNTIF. COUNTA counts the number of non-empty cells in a range, which is used to calculate the total number of participants. COUNTIF counts the number of cells that meet a certain condition, which is used to count the gender.

B12: =COUNTIF($A$2:$A$7,"F")

B13: =COUNTIF($A$2:$A$7,"M")

B14: =COUNTA($A$2:$A$7)

The formulas in C12 to C14 are simple division formulas.

In Participants (grade), the same two functions mentioned earlier are used, but the formulas are written slightly differently.

B18: =COUNTIF($B$2:$B$7,"9")

B19: =COUNTIF($B$2:$B$7,"10")

B20: =COUNTIF($B$2:$B$7,"11")

B21: =COUNTIF($B$2:$B$7,"12")

B22: =COUNTA($A$2:$A$7)

The formulas in C18 to C22 are simple division formulas.

In Score (gender), the SUM and SUMIF formulas are needed. SUM calculates the total of a fixed range of cells, while SUMIF sums the cells in a range that meet a specific condition.

F12: =SUMIF($A$2:$A$7,"F",$C$2:$C$7)+SUMIF($A$2:$A$7,"F",$D$2:$D$7)

F13: =SUMIF($A$2:$A$7,"M",$C$2:$C$7)+SUMIF($A$2:$A$7,"M",$D$2:$D$7)

F14: =SUM(C2:C7,D2:D7)

The formulas in G12 to G14 are simple division formulas.

In Score (grade), the same two functions mentioned earlier are used, but the formulas are written slightly differently.

F18: =SUMIF($B$2:$B$7,"9",$C$2:$C$7)+SUMIF($B$2:$B$7,"9",$D$2:$D$7)

F19: =SUMIF($B$2:$B$7,"10",$C$2:$C$7)+SUMIF($B$2:$B$7,"10",$D$2:$D$7)

F20: =SUMIF($B$2:$B$7,"11",$C$2:$C$7)+SUMIF($B$2:$B$7,"11",$D$2:$D$7)

F21: =SUMIF($B$2:$B$7,"12",$C$2:$C$7)+SUMIF($B$2:$B$7,"12",$D$2:$D$7)

F22: =SUM(C2:C7,D2:D7)

The formulas in G18 to G22 are simple division formulas.

In columns H and I, you can merge the two adjacent cells and then use the SUM formula along with a simple division formula to combine and calculate the statistical data for 9th-10th grade and 11th-12th grade students.

> Is there a way to use the COUNTIF twice in a formula?

You use COUNTIFS()

COUNTIFS() works like COUNTIF(), except there are multiple ranges that are checked, and only cells that match all checks are summed.

For example, if Column A contains Gender, and Column D contains the score, then:

=COUNTIFS($A,"M", $D,"")

will tell you how many blank cells there are in column D that match rows with an 'M' in column A

Or, to count how many valid (non-blank) male scores:

=COUNTIF($A,"M") - COUNTIFS($A,"M", $D,"")

Where COUNTIF($A,"M") tells you how many Males, from which you subtract the number of blank scores.

For this kind of analysis, I highly recommend taking a look a Pivot Tables. They are easy to use.

For example, in five minutes, with no formulas, just clicks and drags, I produced the following three tables:

To create a Pivot Table click in the table with the data and from the menu choose Organize > Create Pivot Table > On Current Sheet.

Then in the Pivot Options pane at the right drag tokens down into to the boxes below. Create multiple Pivot Tables based on the same data table. Try different things. Click the circled i's to see what is behind them. Your original data will not be disturbed.

Create a pivot table in Numbers on Mac - Apple Support

Intro to pivot tables in Numbers on Mac - Apple Support

and more at Help > Numbers Help in your menu.

There is also a Pivot Table Basics template at File > New in the menu.

If you need to show the results to others then you may not succeed at getting exactly the right look. In that case just copy-paste Pivot Table results into a "regular" Numbers table and format as desired.

If you change or add data to your original table on which the Pivot Tables are based, you can update each Pivot Table by clicking it and from the menu choosing Organize > Refresh Pivot Table.

SG