How to count a column meeting a criteria

[hsandeep]

A column W5:W9999 contains attendance from either of 0, 1, ........31 of employees.
How to count no of employees meeting certain criteria like:
Total no. of emp with values >0.
Total no. of emp with values >0 but < 16.

 

[buxtongt]

=COUNTIF(W5:W9999,">0") for the first question

 

[hsandeep]

It WORKS.
But I am not sure whether it also counts '#N/A' or '#VALUE!' or not? Please reply. I want that the formula should not count '#N/A' or '#VALUE!'.
Plus, How to count if values >0 but less <16. Here also the formula should not count '#N/A' or '#VALUE!'.

 

[hsandeep]

buxtongt
The formula is ALSO COUNTING appearances of '#N/A' or '#VALUE!'.
I want the formula should not count '#N/A' or '#VALUE!'.
Plus, How to count if values >0 but less <16. Here also the formula should not count '#N/A' or '#VALUE!'.

 

[Vidar]

Hi

Assuming the values and ERROR's are in the same W column:
=COUNTIFS(W5:W999,">"&0;W5:W999,"<"&16)

Vidar

 

[hsandeep]

=COUNTIFS(W5:W999,">"&0;W5:W999,"<"&16)

NO it did not work. The Excel was not accepting the formula & returning back to the formula bar & blinking on 0.

 

[Vidar]

Hi

Sorry, I forgot to replace a semicolon in the middle of the formula with a comma.
(In Norway we use semicolon as seperator between arguments)
It should look like this:
=COUNTIFS(W5:W999,">"&0,W5:W999,"<"&16)

Vidar

 

[bosco_yip]

=COUNTIFS(W5:W999,">"&0,W5:W999,"<"&16)

Vidar

The "&" can be omitted

=COUNTIFS(W5:W999,">0",W5:W999,"<16")

Regards

 

[bosco_yip]

If your datas are in whole number, you can use

=FREQUENCY(W5:W999,{15,0})

Regards

 

[hsandeep]

If your datas are in whole number, you can use

=FREQUENCY(W5:W999,{15,0})

Regards

Datas are in whole number.
But #N/A or #VALUE! also appears.
What to do?