Use of Array formula to count the number of records in excel that meet a set of conditions.Remeber, that writing array formula is same as any other formula except that you press Ctrl+Shift+Enter.
For example:
The worksheet carries three columns, A, B and C.A is for name of student, B for marks and C for gender(M/F).
Your sheet looks like
A ------B -----C
Mary -- 60 -- F
Sam -- 70 -- M
Tim -- 65 -- M
Rudy -- 80 -- F
Jack -- 75 -- M
For this, you want to see Number of Males getting more than 70.
The formula will be(Write this in the cell you want the result):
=SUM(IF($B$2:$B$6> =70, IF($C$2:$C$6="M",1,0)))
Press CTRL+SHIFT+ ENTER.
The result in this case is 2.
No comments:
Post a Comment