I assume there is a way in excel but I honestly don't know. But I would rather use python just because you can use the same code in case you need to perform the same task again in the future or if you need to query a different range. It's just a matter of using xlrd: https://pypi.org/project/xlrd/ and iterating the columns, updating a counter variable as you get hits.

You can use Pandas to read your Excel file with the function pd.read_excel(), for example with this dataframe:

 | ID              | Gene   | Answer   |
 |-----------------|--------|----------|
 | ENSG00000000004 | YBGRXF | Yes      |
 | ENSG00000000542 | BPHRXT | No       |
 | ENSG00000000320 | OLRGZB | No       |
 | ENSG00000000668 | OQC62N | No       |
 | ENSG00000003876 | PWP8ME | Yes      |
 | ENSG00000003979 | URP4GF | Yes      |
 | ENSG00000001919 | 4HBVVA | No       |
 | ENSG00000006510 | MECXQH | Yes      |
 | ENSG00000005306 | V5SGVX | No       |
 | ENSG00000004722 | HSVPJ8 | No       |
 | ENSG00000007613 | A2WNR4 | Yes      |
 | ENSG00000002934 | TDF8QK | Yes      |
 | ENSG00000002125 | 5MVEY1 | No       |
 | ENSG00000005561 | U6Y4P3 | No       |
 | ENSG00000001621 | V111IT | No       |
 | ENSG00000003284 | O3GICF | No       |
 | ENSG00000004562 | BKE142 | Yes      |
 | ENSG00000005886 | CRSRYC | Yes      |
 | ENSG00000004493 | P20JCS | Yes      |
 | ENSG00000004127 | TMP83G | No       |

You can use this code:

 res_A2_A6 = (df.set_index("ID")["Answer"]
                .iloc[0:4]
                .value_counts()
                .to_frame(df["ID"].loc[0])
                .T)

 res_A7_A11 = (df.set_index("ID")["Answer"]
                 .iloc[5:9]
                 .value_counts()
                 .to_frame(df["ID"].loc[5])
                 .T)

 res_A12_A20 = (df.set_index("ID")["Answer"]
                  .iloc[10:18]
                  .value_counts()
                  .to_frame(df["ID"].loc[10])
                  .T)

To select specific columns and different ranges [0:4] to count your Answer column. In the end you can concatenate your results in a new dataframe:

results = pd.concat([res_A2_A6, res_A7_A11, res_A12_A20], axis=0)

Something like this:

 |                 |  No  |  Yes |
 |-----------------|------|------|
 | ENSG00000000004 |   3  |    1 |
 | ENSG00000003979 |   2  |    2 |
 | ENSG00000007613 |   4  |    4 |