COUNTIF and COUNTIFS giving different results

%3CLINGO-SUB%20id%3D%22lingo-sub-2641275%22%20slang%3D%22en-US%22%3ECOUNTIF%20and%20COUNTIFS%20giving%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641275%22%20slang%3D%22en-US%22%3E%3CP%3EWindows10Pro%20and%20Microsoft%20365%20Apps%20for%20Business.%20Excel%20Ver%202107.%3C%2FP%3E%3CP%3ECOUNTIF%20in%20one%20formula%20does%20not%20counts%20any%20of%20the%20cells%2C%20while%20the%20same%20formula%20on%20another%20column%20gives%20the%20correct%20answer%3A%201)%26nbsp%3B%3DCOUNTIF(C3%3AC282%2C%22%26lt%3B60%22)%202)%26nbsp%3B%3DCOUNTIF(F3%3AF282%2C%22%26gt%3B4%22)%3C%2FP%3E%3CP%3EI%20changed%20the%20numbers%20from%20General%20to%20Number%20and%20get%20same%20result.%26nbsp%3B%20Same%20issue%20with%20COUNTIFS.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2641275%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2641297%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20and%20COUNTIFS%20giving%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641297%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126971%22%20target%3D%22_blank%22%3E%40PhilipLibman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20demonstrating%20the%20problem%3F%20Don't%20include%20any%20sensitive%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2641401%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20and%20COUNTIFS%20giving%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20please%20see%20attached%20file.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2641540%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20and%20COUNTIFS%20giving%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126971%22%20target%3D%22_blank%22%3E%40PhilipLibman%3C%2FA%3E%26nbsp%3BFirst%20of%20all%2C%20the%20%22numbers%22%20in%20column%20A%20and%20B%20were%20texts.%20Fixed%20that%20with%20Text-to-columns.%20Then%2C%20the%20second%20formula%20contains%20a%20typo.%20You%20used%26nbsp%3BCOU%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EI%3C%2FSTRONG%3E%3C%2FFONT%3ENTIF.%20Hence%20the%20%23NAME%3F%20error.%20Remove%20the%20letter%20%22I%22%20marked%20in%20red%20and%20it%20shall%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2641653%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20and%20COUNTIFS%20giving%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2641653%22%20slang%3D%22en-US%22%3EThanks!%20The%20typo%20is%20a%20silly%20error.%20I%20did%20change%20from%20text%20to%20numbers%2C%20but%20did%20it%20in%20%22Format%20Cells%22%20and%20it%20didn't%20take.%20Only%20Text%20to%20Column%20worked%20and%20allowed%20the%20formula%20to%20calculate%20correctly.%3CBR%20%2F%3EMuch%20appreciated.%3C%2FLINGO-BODY%3E
Occasional Contributor

Windows10Pro and Microsoft 365 Apps for Business. Excel Ver 2107.

COUNTIF in one formula does not counts any of the cells, while the same formula on another column gives the correct answer: 1) =COUNTIF(C3:C282,"<60") 2) =COUNTIF(F3:F282,">4")

I changed the numbers from General to Number and get same result.  Same issue with COUNTIFS.

Thanks

 

 

 

6 Replies

@PhilipLibman 

Could you attach a sample workbook demonstrating the problem? Don't include any sensitive information.

@Hans Vogelaar 

Yes please see attached file. Thanks

@PhilipLibman First of all, the "numbers" in column A and B were texts. Fixed that with Text-to-columns. Then, the second formula contains a typo. You used COUINTIF. Hence the #NAME? error. Remove the letter "I" marked in red and it shall work.

 

See attached.

 

Thanks! The typo is a silly error. I did change from text to numbers, but did it in "Format Cells" and it didn't take. Only Text to Column worked and allowed the formula to calculate correctly.
Much appreciated.

@PhilipLibman You're welcome. And just bare in mind that formatting a cell as Number doesn't change the content of the cell to a number if it had been entered/imported as a text. 

@Riny_van_Eekelen 

You learn something new every day!  Thanks again.