COUNT.IF does apply here?

[atferrari]

I was asked to help a student with the basics of Excel. It's embarrasing but I couldn't solve this:

There is a list of 69 students, males and females, (M and F) of varying ages.

I am asked to tell in a cell, the number of females older than 17.

I tried to use the function COUNT.IF, as follows:

=COUNT.IF(C2:C70;AND(E2 : E70="F";D2 : D70>17))

range C2:C70 with the names (I expect the function to count over this range's cells)
range D2: D70 with the ages
range E2:E70 with F or M

Please note that I tried to translate the functions that in Spanish show up as (longtime I don't use an English version of Excel):

CONTAR.SI (conditional counting)
Y (direct equivalent of a boolean AND)

What I am doing wrong?

The usual (useless) disclaimer "I used to be profficient in this..." also applies....

 

[Pommie]

The only way I know to do this is with an array function.

Enter the formula,

=SUM((D2:D70>17)*(E2:E70="f"))

and then press ctrl-shift-enter to enter it. Excel will place {} braces around the formula and treat it as an array. Whenever you access this cell you must press ctrl-shift-enter or it will stop working.

Mike.
I had to enter it in a code box or the BB software put smiley faces in it.

 

[atferrari]

Other solution?

Thanks for that Mike,

At this stage of the starting level of that student, matrix maths is not an option.

I found a less-than-perfect solution solving one of the logic tests recording the resulting TRUE/FALSEs in a column and then applying COUNTIF to it.

Excel 2007 comes with COUNTIFS which solves this complex problems straight, but I run on the 2003 version.

If not resorting to yours nor to mine, do you think there is any other elegant solution?

 

[Pommie]

I'm afraid that's the only way I know how to do it without an intermediate column.

BTW, Excel 2003 is luxury - I'm still on Office 2000!!

Mike.