Forum Discussion

anthwny's avatar
anthwny
Copper Contributor
Mar 08, 2024

COUNTIFS Non-Empty Cells

Hi! I'm new to excel and want to use COUNTIFS, but I don't want it to count empty cells. 

 

My formula is like this:

 

=COUNTIFS($C$4:$C$10001;H4;$B$4:$B$10001;G4)

 

And it works fine when everything has values, but I don't want it to count empty cells, what should I add?

  • binodranabhat's avatar
    binodranabhat
    Copper Contributor

    anthwny I did not understand what do you mean by counting empty cells. 

    BCGH

    Result

    AAABBBAAABBB2
    AAABBB  

    0

         

     

    =COUNTIFS($C$5:$C$10002,H4,$B$5:$B$10002,G4)

    When you use the formula it will evaluate both expression as AND function ie
    AND( ($C$5:$C$10002,H4), ($B$5:$B$10002,G4))
    returning TRUE , TRUE then only the value starts adding up. 

    also it does not count the empty cells that 

     

  • visolanki10's avatar
    visolanki10
    Copper Contributor
    To count non-empty cells using the COUNTIFS function in Excel, you can add additional criteria to check if the cells are not empty. You can use the "<>" operator to specify "not equal to" empty or blank cells.

    =COUNTIFS($C$4:$C$10001; H4; $C$4:$C$10001; "<>"; $B$4:$B$10001; G4; $B$4:$B$10001; "<>")
    • anthwny's avatar
      anthwny
      Copper Contributor

      visolanki10 Hi! Thank you! I have tried to paste the formula you wrote, but I still get the same result, it still counts the empty ones. Should I add something else or could it be a locale-issue?

      • beerExcelguy207's avatar
        beerExcelguy207
        Copper Contributor

        In order to find the count of empty cells in a CountIfs formula, the criteria should also include &""

        I.e. =COUNTIFS([criteria range1],"<>"&"",[criteria range2].....)

Resources