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....

 

From what I can tell by looking at the HELP file in EXCEL, the AND function does not permit the use of range statements like A1:A100.

Another observation is that "COUNT.IF" is not defined. The function name should be "COUNTIF" instead.

hgmjr

 

Hola Agustin,
Here is a formula that will do what I think you want it to do that uses the COUNTIFS function.

=COUNTIFS(d2:d70,">17",e2:e70,"=F")

It will count all of the students who are older than 17 and who are female.

This formula uses the same ranges that you gave--ages in cells d2 to d70, genders in cells e2 to e70.

This function is available in MS Excel 2007, but might not be available in older versions. Since I don't know what version you are using, I can't say for sure that it's available in the version you are using. I didn't find a function named COUNT.IF in my version.

Hasta luego,
Mark

 

Yes, I should have say COUNTIF.

No, my version is 2003 so COUNTIFS is not available. I wonder what is the Spanish equivalent?

Any suggestion on how to do it?

Hgmjr,

The criteria, (I think) should accept TRUE or FALSE coming from a AND. But I don't know how to do it.

Thanks for replying.

 

Agustin,
I also have Excel 2003, and I don't see any function in it that will do what you want. As hgmjr pointed out, the AND function doesn't take a range argument. There is a COUNTIF function, but from the examples I looked at, you can use it only with one range.

Unless there was something I missed, you can't do what you want using Excel 2003.

 

Thanks for replying to you both.

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.

If not resorting to my solution, could you suggest any other two-steps solutions?