Forum Discussion

PhilipLibman's avatar
PhilipLibman
Copper Contributor
Aug 12, 2021
Solved

COUNTIF and COUNTIFS giving different results

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) =COUN...
excel
Formulas and Functions
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 12, 2021

    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.

     

HansVogelaar's avatar
HansVogelaar
MVP
Aug 12, 2021

PhilipLibman 

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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 12, 2021

    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.

     

    • PhilipLibman's avatar
      PhilipLibman
      Copper Contributor
      Aug 12, 2021
      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.
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Aug 12, 2021

        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. 

Resources