COUNTIF criteria

New Contributor

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)



4 Replies


COUNTIF with a cell reference in the Criteria argument should work:




What does A102 contain?

@Hans Vogelaar 


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

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


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.