Frequency with criteria

[degree01]

I have used the "Frequency" to count marks within the given range i.e 0-23, 24-29, 30-53

Column A contains the following student marks assuming first mark is in A1

22
21
23
29
50

Column B has Bin value, 23 is in B1. Bin value sets the maximum marks in a range.

23
29
53

column C contains the below formulas, first formula in C1, second in C2 etc

{=FREQUENCY(A:A,B1:B3)}
{=FREQUENCY(A:A,B1:B3)}
{=FREQUENCY(A:A,B1:B3)}

this give me result as below, first number in C1, second in C2 etc

3 (3 students received marks from from 0 to 23)
1 (1 students received marks from from 24 to 29)
1 (1 students received marks from from 30 to 53)

Column D contains gender, male or female.

Now what I need is to find out how many of the students from the range 0 to 23, 24 to 29 and 30 to 52 are male or female using column D. For example 0 to 23 range has 3 students, two of them are male and one female.

Column E should show Male, and F should show Female.

Hope that makes sense, I will really appreciate if anyone can help. Thank you.

 

[Marcelo Branco]

Maybe something like

	A​	B​	C​	D​	E​	F​
1​	Values​	Bin​	Freq​	Gender​	Freq M​	Freq F​
2​	22​	23​	3​	M​	2​	1​
3​	21​	29​	1​	M​	1​	0​
4​	23​	53​	1​	F​	0​	1​
5​	29​			M​
6​	50​			F​

<TBODY>
</TBODY>

Array formula in C2:C4
=FREQUENCY(A2:A6,B2:B4)

Array formula in E2:E4
=FREQUENCY(IF(D2:D6="M",A2:A6),B2:B4)

Array formula in F2:F4
=FREQUENCY(IF(D2:D6="F",A2:A6),B2:B4)

All formulas must be confirmed with Ctrl+Shift+Enter

M.

 

[degree01]

Hi Marcelo I am alway from my laptop at the moment, using tablet, but this is exactly what i want, i will try the formulas out tmr and see if it works for me. Thanks alot.

 

[Marcelo Branco]

Hi Marcelo I am alway from my laptop at the moment, using tablet, but this is exactly what i want, i will try the formulas out tmr and see if it works for me. Thanks alot.

You are welcome! Thanks for the feedback.

M.