COUNTIF criteria

Question

I am trying to use CountIF function. My data I am trying to count has periods in the text i.e. A.9.1.1

When I hard code the criteria the function works =COUNTIF(Rsk2Ctl!B2:B112,"A.9.1.1")

But when I try to program the if does not work =COUNTIF(Rsk2Ctl!B2:B112,A102)

Why

hansvogelaar · Answer

MyMER&nbsp;
COUNTIF with a cell reference in the Criteria argument should work:
&nbsp;

&nbsp;
What does A102 contain?

mymer · Answer

HansVogelaar&nbsp;&nbsp;I attached spread sheet and consolidated data into one sheet.Row 102 show how the reference to I102 does not work but when hard coded "A.9.1.1" it does

mymer · Answer

I found what is making it not work. the problem is in the data of the reference cell . When I replace the text sting in the I102 cell by typing A.9.1.1 it works. I tried the CLEAN() function but it did not work. so I just need to go through the spreadsheet and update the values because there is some format error that breaks the countif function in the data

hansvogelaar · Answer

MyMER&nbsp;
The values in column I all have a space at the end, but the values in columns B to H don't.
Select column I.
Press Ctrl+H to activate the Replace dialog.
Enter a space in the 'Find what' box and leave the 'Replace with' box empty.
Click 'Replace All'.
The formula in J102 will now work.

Forum Discussion

COUNTIF criteria

4 Replies