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


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Values​

[/TD]
[TD]

Bin​

[/TD]
[TD]

Freq​

[/TD]
[TD]

Gender​

[/TD]
[TD]

Freq M​

[/TD]
[TD]

Freq F​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

22​

[/TD]
[TD]

23​

[/TD]
[TD]

3​

[/TD]
[TD]

M​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

21​

[/TD]
[TD]

29​

[/TD]
[TD]

1​

[/TD]
[TD]

M​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

23​

[/TD]
[TD]

53​

[/TD]
[TD]

1​

[/TD]
[TD]

F​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

29​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

M​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

F​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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.