Counting Non-Blank Cells for Selected Columns

Count Non-Blank Cells in Column

Count Non-Blank or Non-Empty Cells in a Column

• The code was used in a new workbook. In Sheet1, in cell A2 I entered the formula ="" and in cell A3 I entered a single quote ('). These two cells appear to be empty, but they are not. They are blank.

Sub CountNonBlanks()

    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Sheet1").Range("A2:A30000")

    ' Counts cells that are not empty, do not contain a formula
    ' that evaluates to an empty string (e.g. '=""')
    ' and do not contain a single quote.
    Dim NonBlanks As Long
    NonBlanks = rg.Cells.Count - Application.CountBlank(rg)
    
    ' Counts cells that are not empty.
    Dim NonEmpties As Long
    NonEmpties = Application.CountA(rg)

    Debug.Print rg.Address(0, 0), NonBlanks, NonEmpties

End Sub

• The result in the Immediate window was:

  A2:A30000      0             2 
  

How to 'count' number of non-empty values in a single row across multiple columns in a dataframe

If you are talking about missing values in R, it's represented in capital letter NA instead of na, otherwise, R will treat it as a string, which is not empty.

Also, I have artificially included some Name in your df to act like each row represents one Name, and a artificial Comp5 which includes some NAs but will not be included in the calculation.

rowSums() as its name suggests, calculates the sum of the row.

is.na(df[, 2:4]) makes it only counts the NA in df from column 2 to column 4.

df <-read.table(header = T, 
                text = 
                  "Name Comp1  Comp2  Comp3  Comp4  Comp5
                A 0.5      0.4    NA     0.6  NA
                B 0.6      NA     NA      0.7 1
                C NA       0.4    NA      1.1 NA")

df$Count_NA <- rowSums(is.na(df[, 2:4]))

Output

  Name Comp1 Comp2 Comp3 Comp4 Comp5 Count_NA
1    A   0.5   0.4    NA   0.6    NA        1
2    B   0.6    NA    NA   0.7     1        2
3    C    NA   0.4    NA   1.1    NA        2

COUNT non-empty cells in each column of a table with condition

=SUMPRODUCT((A2:A7=H2)*(B2:F7<>""))

Where:

A2:A7 is your name column

H2 is your name equal to criteria

B2:F7 is there non blank cells you want to count

Count the number of non empty columns in R

We may use vectorized rowSums on a logical matrix

data$Total <- rowSums(!is.na(data[1:3]))
data$Total
[1] 3 2 2 1 1

Is there a way to count not blank cell in a row with a specific column criteria from a header

O2: =COUNTIFS($A$1:$N$1,"s*",A2:N2,"<>")

and fill down

Count Non blanks cells form column based on month in another column and sum the values of cells?

Adjust you SUMPRODUCT to include a check for blanks:

=SUMPRODUCT((Month(A2:A7)=1)*(B2:B7<>""))

Then for the sum:

=SUMPRODUCT((Month(A2:A7)=1)*B2:B7)

---

Related Topics