Ignoring the character X

[Dannysamworth]

Hi,

I am trying to make a formula that will ignore the character x.

I am creating a timesheet where in a row of cells I enter initials of the project. On the total for that week it adds up the cells with text and gives the number of days the operative worked.

The formula I have used is

=COUNTIF(D6:J6,”**)

This works absolutely fine when the operative has worked all week, however I want to put an X in the cell that he has not worked and this not be recognised and counted.

Is there a way of doing this ?
Many thanks

Danny Samworth.

 

[mart37]

=COUNTIF(D6:J6,”*")-COUNTIF(D6:J6,”X")

 

[Dannysamworth]

=COUNTIF(D6:J6,”*")-COUNTIF(D6:J6,”X")

Wow, worked perfectly.
Thank you so much for your help.

 

[FormR]

Hi, another option you could try.

=COUNTIFS(D6:J6,"*",D6:J6,"<>X")

Or if you don't have any numbers in the range that you are trying to exclude from the count.

=COUNTIFS(D6:J6,"<>X")

 

[Dannysamworth]

Hi, another option you could try.

=COUNTIFS(D6:J6,"*",D6:J6,"<>X")

Or if you don't have any numbers in the range that you are trying to exclude from the count.

=COUNTIFS(D6:J6,"<>X")

Hi, thank you for you option,
I found that with this formula I can enter the character X along with other letters, foe example the initials KHX and this is recognised.

where as when I enter just the X for non attendance then it’s not counted.

Exactly what I needed,

thanks very much for your help.