Count different words and numbers in same range to give total

Hi.

I think this should be a reasonably simple request but i am really struggling to get this to work. I am doing a roster for work which is really causing me an headache! Imagine that the range is B3:H3, in the cells there would be:-

B3 - AN
C3 - AD
D3 - AD
E3 - R
F3 - R
G3 - 1
H3 - 1

I then want the formula to work out in I3 the total count of the range but not to include any cell that contains R because these indicate rest days which i dont want to be part of the count.

Any help would be really appreciated.

Thanks.

NIN3884.

Originally Posted by hemesh

try below in I3
=SUMPRODUCT(--(B3:H3<>"R"))

Thanks Hamesh, brilliant! I thought it would be something reasonably simple, thanks for the prompt reply.

Originally Posted by sktneer

Another way.......

Please Login or Register  to view this content.

That works too, thanks sktneer. Really appreciate the prompt response.

Originally Posted by sktneer

Glad to help you. Thanks for the feedback.

Hi Sktneer.

Apologies, i have marked my question as solved which it was for my initial question (thanks again!). However when i try to calculate the total staff on duty for each day the range is B5:B61, the cell used is B62. I want to calculate only cells in B62 that count up "1", "AD","AN". I thought that a simple solution was to use the COUNTIFS function but this keeps returning a value of zero.

Can you help?

Thanks in advance.