Hello friends. Please help ! COUNTIF formula ?Solved

Question

Hello friends. Please help !

I want a formula ( maybe COUNTIF? ) to count how many "W" and "D" and "L" are found per each range: "B3:B12", "D3:D12" and "F3:F12".

But to count only in the first 5 cells with value (that are NOT empty blank cells).

I don't want to count all "W" and "D" and "L" found in the entire range.

I want to count "W" and "D" and "L" on the first 5 filled cells, skiping the blank cells.

The formulas must be separate, meaning a formula to count just the value "W", another formula to count just the value "D", another formula to count just the value "L".

In short, to count in the first 5 cells that are not blank:
- how many "W"
- how many "D"
- how many "L"

I want to obtain the count from the green examples.

I DO NOT want to obtain the count from the red examples.

Please see below url image. Thank you !

https://ibb.co/71Twd93

TrowaD · Accepted Answer

Hi Realest25,

I can't think of (a) function(s) that can do that specific task, so I created one for you.

This is the code:
Public Function CountVal(mRange As Range, mVal As String) As Integer
Dim x As Integer
For Each cell In mRange
    If cell.Value <> vbNullString Then
        x = x + 1
        If cell.Value = mVal And x < 6 Then CountVal = CountVal + 1
    End If
Next cell
End Function

How to implement the code to use the custom function:

 Open VBE: ALT+F11
 Go to the top menu's and click on Insert, then Module
 Place the above code in the big white field
 Close the VBE window

How to use the function:
=CountVal(B$3:B$12;"W") for B15
=CountVal(B$3:B$12;"D") for B16
=CountVal(B$3:B$12;"L") for B17
Drag these cells to the right and clear the results for columns C and E.

Best regards,
Trowa

Hello friends. Please help ! COUNTIF formula ?

1 reply

Your reply

Subscribe To Our Newsletter!