How Use COUNTIF to Count Non Empty Cells in Excel

[Dossfm0q]

How to Use COUNTIF to Count Non Empty Cells in Excel

Sub CountNonBlank()


    Cells.Clear
    Range("A1:A7") = Application.Transpose(Array("A", "B", "C", "D", "E", "", ""))
    
    Range("A10").Formula = "[COLOR=#ff0000][B]=COUNTIF(A1:A7,""<>"""""")[/B][/COLOR]" ' [COLOR=#ff0000]Rong result[/COLOR]
    
    Range("A12").Formula = "=[COLOR=#006400][B]ROWS(A1:A7) * COLUMNS(A1:A7)-COUNTBLANK(A1:A7)[/B][/COLOR]" ' [B][COLOR=#008000]Wright result[/COLOR][/B]
            '''''''''''''''''''''''''''''''''''''''''
            With Range("B10")
            .Value = "O"
            .Font.Name = "Wingdings 2"
            End With
            With Range("B12")
            .Value = "P"
            .Font.Name = "Wingdings 2"
            End With
            ''''''''''''''''''''''''''''''''''''''
End Sub

 

[mikerickson]

Have you tried =COUNTA(A1:A7)

 

[Dossfm0q]

Yes Sir I did

Public Sub CountNonEmptyCell()




    Cells.Clear
    [A1] = "A,BB,CCC,DDDD,EEEEE,FFFFFF"
    
    Range("A3:A12").FormulaArray = "=IFERROR(MID(A$1,FIND(""|"",SUBSTITUTE("",""&A$1&"","","","",""|"",(ROW()-ROW(B$3)+1))),FIND("","",A$1&"","",FIND(""|"",SUBSTITUTE("",""&A$1&"","","","",""|"",(ROW()-ROW(B$3)+1))))-FIND(""|"",SUBSTITUTE("",""&A$1&"","","","",""|"",(ROW()-ROW(B$3)+1)))),"""")"


   [COLOR=#ff0000][B] [A13] = "=COUNTA(A3:A12)"[/B][/COLOR]
    Range("A14").Formula = "=ROWS(A3:A12) * COLUMNS(A3:A12)-COUNTBLANK(A3:A12)"
End Sub

 

[Aladin Akyurek]

=countifs(a:a,”?*”)

=countifs(a:a,”<>”)

 

[Dossfm0q]

still has same err

Thanks for respond

 

[mikerickson]

You say they all have the same error, but don't describe the error.
What do those approches do that isn't right?

 

[Aladin Akyurek]

still has same err

Thanks for respond

What is giving the error? And what is the error?

 

[Dossfm0q]

I am apologize! I get busy
I don't main There is an Error, I main the empty cells of array formula are uncountable with this function.

I am sorry about misunderstand from my side.

 

[Aladin Akyurek]

I am apologize! I get busy
I don't main There is an Error, I main the empty cells of array formula are uncountable with this function.

I am sorry about misunderstand from my side.

What do you want to count?

If empty/blank cells:

=COUNTIFS(A2:A100,"=")

If non-blanks cells:

=COUNTIFS(A2:A100,"<>")