Monday, November 15, 2004

MS Excel: Record Counting Based on Multiple conditions

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: