# How to use multiple criteria with COUNTIF?

INPUT:

This is the format of the OUTPUT desired:

OUTPUT for example INPUT:

The problem is, I don't know how to use COUNTIF with several criteria.

The "dream function" [that doesn't work] would look like this in cell D3:

``````=(COUNTIF(\$M\$2:\$M\$65535;boy) AND (COUNTIF(\$N\$2:\$N\$65535;">0")-COUNTIF(\$N\$2:\$N\$65535;">4")) AND (COUNTIF(\$O\$2:\$O\$65535;WILMS))
``````

Any ideas?

-
use the `COUNTIFS` formula in Excel 2007. In prior versions, you will have to use an array formula – JMax Jan 11 '12 at 10:56
It appears there are multiple values in some cells of column N. The formula is not going to be able to see them as separate values, therefore will not see 0,4 as 0 and 4. These need to be in there own rows/cells. – CharlieRB Jan 11 '12 at 14:52
Is that not continental notation for 3.4 ? Here in Spain the , and . are transposed. 1.000,2 is just over a thousand. – BrianA Jan 11 '12 at 15:59

Add a column that classifies by range (say P2=`IF(N2>=9,"R4",IF(N2>4,"R3",IF(N2>1,"R2","R1")))` & copy down) then use a pivot table. With some appearance modification (and using Excel 2007) the result seems close enough to serve: