Is this possible?

[alombia]

Hi all,

I have the following sheet:

	A	B	C	D	E	F	G	H	I
1	Name	Gender	Level 1	Level 2	Level 3	Level 4	Level 4	Level 5
2	John	M	A*	B	A	A	F	C
3	Ali	M	A	C	B	D	E	C
4	Mike	M	B	D	A*	C	A	A
5	Pauline	F	C	E	A	B	A*	A
6	Harris	M	D	F	D	A*	B	A*
7	Nasima	F	B	G	G	E	C	B
8	Luba	F	C	B	F	A	D	C
9
10

<tbody>
</tbody>

I have named A1:H8 as Scores.
I am counting the occurrences of each of the letters (A, B, C, etc). That is easy enough for me. However, I would like to exclude rows from this range based on Gender. In others, I would like to first count the occurrence of A in the full range (which i can do). Then count A in the range, but this time exclude any row which has M in the Gender column. Hope this makes sense.
Thanks

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Gender	Level 1	Level 2	Level 3	Level 4	Level 4	Level 5
2	John	M	A*	B	A	A	F	C
3	Ali	M	A	C	B	D	E	C
4	Mike	M	B	D	A*	C	A	A
5	Pauline	F	C	E	A	B	A*	A
6	Harris	M	D	F	D	A*	B	A*
7	Nasima	F	B	G	G	E	C	B
8	Luba	F	C	B	F	A	D	C
9
10		3
Sheet

 

[mopp1]

If you want to include the A*s in the count use:
=SUMPRODUCT((MID(C2:H8,1,1)="A")*(B2:B8<>"M"))

 

[Scott Huish]

If you want to include the A*s in the count use:
=SUMPRODUCT((MID(C2:H8,1,1)="A")*(B2:B8<>"M"))

LEFT would be more succinct:

=SUMPRODUCT((LEFT(C2:H8)="A")*(B2:B8<>"M"))