COUNTIF criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2546796%22%20slang%3D%22en-US%22%3ECOUNTIF%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546796%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20CountIF%20function.%20My%20data%20I%20am%20trying%20to%20count%20has%20periods%20in%20the%20text%20i.e.%20A.9.1.1%3C%2FP%3E%3CP%3EWhen%20I%20hard%20code%20the%20criteria%20the%20function%20works%26nbsp%3B%3DCOUNTIF(Rsk2Ctl!B2%3AB112%2C%22A.9.1.1%22)%3C%2FP%3E%3CP%3EBut%20when%20I%20try%20to%20program%20the%20if%20does%20not%20work%26nbsp%3B%3DCOUNTIF(Rsk2Ctl!B2%3AB112%2CA102)%3C%2FP%3E%3CP%3EWhy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2546796%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2546901%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1103250%22%20target%3D%22_blank%22%3E%40MyMER%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECOUNTIF%20with%20a%20cell%20reference%20in%20the%20Criteria%20argument%20should%20work%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0585.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F295713i368133BCE910C449%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0585.png%22%20alt%3D%22S0585.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20does%20A102%20contain%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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)

Why

 

4 Replies

@MyMER 

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

 

S0585.png

 

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

@MyMER 

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.