Reputation: 113
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
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