Countif problems not calculating correctly

[Jayne Foot]

Good afternoon all

I am having issues with a Count If formula

=COUNTIF('From April 2017'!F2:F801,"On call manager") This one works fine
=COUNTIF('From April 2017'!F1:F802,"OOA") This one and =COUNTIF('From April 2017'!F3:F803,"=YFS")gives a 0 count when they should be 10 and 1


There are no differences except the criteria and I have the same formula for 33 separate cells and all the others work fine. I have checked that all the cells with the formula have all the same settings but cannot for the life of me work out what is going wrong. I have been on the youtube help sections and they were no help as everything they suggested to check is all fine.

Can someone help - it is driving me mad

Thanks in advance

 

[Rick Rothstein]

I am having issues with a Count If formula

=COUNTIF('From April 2017'!F2:F801,"On call manager") This one works fine
=COUNTIF('From April 2017'!F1:F802,"OOA") This one and =COUNTIF('From April 2017'!F3:F803,"=YFS")gives a 0 count when they should be 10 and 1

There are no differences except the criteria and I have the same formula for 33 separate cells and all the others work fine. I have checked that all the cells with the formula have all the same settings but cannot for the life of me work out what is going wrong.

Is OOA and YFS the only text in the cells or could there be other text in the cells along with them? If the latter, then try this formula instead...

=COUNTIF('From April 2017'!F1:F802,"*OOA*")

 

[Jayne Foot]

Is OOA and YFS the only text in the cells or could there be other text in the cells along with them? If the latter, then try this formula instead...

=COUNTIF('From April 2017'!F1:F802,"*OOA*")

Thanks for your very quick reply - it is the only text

 

[Dryver14]

Also along with the above Where it says YFS in the column put formula = Len(Cell reference)...if more than 3 then you have leading or trailing spaces

 

[Jayne Foot]

Also along with the above Where it says YFS in the column put formula = Len(Cell reference)...if more than 3 then you have leading or trailing spaces

I am sorry but I do not understand - am I putting =LEN(cell ref) in the formula in my original post or am I putting this in the cell where the original YFS is typed

I am by no means an expert on excel so need things explained child like. lol

 

[Dryver14]

what I mean is in an empty cell type = Len( and then select the cell where it says yps)

 

[RoryA]

If Rick's formula:

=COUNTIF('From April 2017'!F1:F802,"*OOA*")

returns something other than 0, then there is more text in those cells, perhaps leading or trailing spaces.

 

[Jayne Foot]

It came up with 1 so does that mean there is something wrong in the cell with the original data

 

[Dryver14]

Yes... not sure I was expecting 1, maybe 4 or 5..... in the cell where it says YPS type YPS in the cell see if you get a different result in the formula

 

[Jayne Foot]

OMG it worked - I was doing the formula in the wrong work sheet but I know what I have been doing - what an idiot - I have been putting a space at the end of YFS and OOA

Thanks so much for your assistance. Very much appreciated