Help needed with counting formula

[rama4672]

I need to input a formula that counts how many times a certain number occures in a column.
The formula that i have got at the moment is: =COUNTIF(E:E,10) That works fine but there are some cells that i do not want it to count, there are people who are on holiday and sick i do not want it to count those occurances, i have tried the following but what this does is it just counts the occurances of hol not how many people who are on 10 hour shifts that are on hol

=COUNTIF(E:E,10)-COUNTIF(F:F,"hol")-COUNTIF(F:F,"sick")

I need it to count just the 10 and i also have another formula that does the same but for 8

Regards

Ian

[Bob Phillips]

Hi Ian,

If you want to count the items in column E that equal 10, and where the corresponding item in F is not hol or sick, use

=SUMPRODUCT(--(E1:E1000=10),--((F1:F1000<>"sick")*(F1:F1000<>"hol")))

obviously you can replace the =10 with = cell_ref when 10 is in cell_ref

[rama4672]

Thank you for the quick reply XLD, I will try that later as i am just going out, I will let you know how I get on


Regards

Ian

[rama4672]

Thank you XLD, I have now tried that and it works great so i will mark this as solved.


Regards

Ian