CODEWITHSUNDEEP
excelexcel-formula
nlimits
nlimits

Reputation: 113

Counting amount of non-empty + no-background cells

I have an excel file, where only two columns have values (A-order number, B-some text/comment) and i need to perform certain counting:

1- Count the amount of cells in B, which are non-empty -> Did it already using COUNTA()

2- Count amount of cells that have "Grey" as a background color.

At the end I will need just a number of non-empty and no-background-color cells.

Upvotes: 1

Views: 437

Answers (1)

CallumDA
CallumDA

Reputation: 12113

This can't really be achieved without VBA. Go to the VBA editor, add a new module and paste this in:

Function CountClear(rng As Range) As Long
    Dim r As Range
    For Each r In rng
        If r.Value <> vbNullString And r.Interior.Color = vbWhite Then
            CountClear = CountClear + 1
        End If
    Next r
End Function

You can then use the formula on the worksheet like a normal COUNTA function, just type CountClear(B1:B100).

Note you will suffer badly if you use whole column references with this function so if you only have data in B1:B100 select that range, or a bit extra -- just not B:B

Upvotes: 1

Related Questions