Hello friends. Please help ! COUNTIF formula ?

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

1 reply

TrowaD
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

2
Thank you very much sir for your reply. I really appreciate your help. God Bless You !
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515 > realest25
Jun 13, 2022 at 11:58 AM
Thanks, good to hear!

Glad you figured out to replace the separator ";" (semicolon) into "," (comma).
0