Forum Discussion
COUNTIF criteria
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 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
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.
- 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
