Solved
realest25 - Jun 7, 2022 at 03:31 PM
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jun 13, 2022 at 11:58 AM

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

Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 9, 2022 at 12:00 PM
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

Thank you very much sir for your reply. I really appreciate your help. God Bless You !