PDA

View Full Version : [SOLVED] Help needed with counting formula



rama4672
05-14-2005, 10:37 AM
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
05-14-2005, 11:06 AM
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
05-14-2005, 11:12 AM
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
05-14-2005, 04:49 PM
Thank you XLD, I have now tried that and it works great so i will mark this as solved.


Regards

Ian