Formula to count partial colored font using wildcards

J

jMz

New Member
Joined
Feb 21, 2024
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Good morning,

I am having a problem counting colored font while using a wildcard for a partial search in a cell. The font color I want to count is "Green" which only appears once in each column to show a winner's name a long with the winning number. Ex: "5. Stephen". The number and name have to stay together, which is where my problem falls, a player can pick multiple numbers in one column but there can only be one winning number and name.
When I type a name "Stephen" in column E3 the formula should count Stephen one time in each column that the name appears as green and over looking the number at the beginning.

I have attached an example. Any ideas would be appreciated, thank you.

Jim

Book1.xlsm
ABCDEFGHIJKLMNO
1NUMBER OF*** Total Spins ***12345
2SpinsWinsPicksPlayer51. Austin Rowe1. Chris Leiter1. Stephen Gross1. Stephen Gross1. Stephen Gross
34#NAME?15Stephen Gross2. Rick Conte2. Daniel Branco2. Daniel Branco2. Daniel Branco2. Stephen Gross
4Function Called3. Rick Conte3. Aaron Burris3. Calvin VanRiper3. Stephen Gross3. Stephen Gross
54. Shane Fair4. Shane Fair4. Shane Fair4. Shane Fair4. John Alger
65. Stephen Gross5. Chris Leiter5. Calvin VanRiper5. Stephen Gross5. Stephen Gross
76. Shane Fair6. Shane Fair6. Shane Fair6. Shane Fair6. Shane Fair
87. Tim and Kim Taylor7. Daniel Branco7. Calvin VanRiper7. Stephen Gross7. Stephen Gross
98. Randy Czapla8. Daniel Branco8. Daniel Branco8. Daniel Branco8. John Alger
109. Shane Fair9. Shane Fair9. Shane Fair9. Shane Fair9. Shane Fair
1110. Stephen Gross10. Sandie Smith10. Sandie Smith10. Sandie Smith10. Calvin VanRiper
1211. Lee Clyde11. Tim and Kim Taylor11. Stephen Gross11. Austin Rowe11. Calvin VanRiper
1312. Sandie Smith12. Daniel Branco12. Daniel Branco12. Daniel Branco12. Shane Fair
1413. Calvin VanRiper13. Lee Clyde13. John Alger13. Tim and13. Laura N
1514. Rick Conte14. Aaron Burris14. Stephen Gross14. Calvin VanRiper14. Laura N
1615. Calvin VanRiper15. Jason Easter15. Stephen Gross15. Calvin VanRiper15. Laura N
17Winning Number5112152
18
19
Feb-18
Cell Formulas
RangeFormula
J2J2=COUNT(K1:O1)
C3C3=CountCellsByFontColor(K1:O15,"*"&E3&"*")
D3D3=COUNTIF(K2:O16, "*"&E3&"*")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G6:G8Cell Value="No"textNO
G6:G14,G16:G50Cell Value="Yes"textNO
G4Cell Value="No"textNO
G4Cell Value="Yes"textNO
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Probably A silly question, but do you have the VBA function in a module?
 
Upvote 0
Anfinsen said:
Probably A silly question, but do you have the VBA function in a module?
Click to expand...
Yes I do, I had copied it into a comment in C3 for view but didn't check to see if it was visible using the Mini XLSM add-on. I've never used it before.
 
Upvote 0
jMz said:
Yes I do, I had copied it into a comment in C3 for view but didn't check to see if it was visible using the Mini XLSM add-on. I've never used it before.
Click to expand...
Please post the actual function so we can check that for you.
 
Upvote 0
VBA Code: 
Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long
  Dim indRefColor As Long
  Dim cellCurrent As Range
  Dim cntRes As Long

  Application.Volatile
  cntRes = 0
  indRefColor = font_color.Cells(1, 1).Font.Color
  For Each cellCurrent In data_range
    If indRefColor = cellCurrent.Font.Color Then
      cntRes = cntRes + 1
    End If
  Next cellCurrent

  CountCellsByFontColor = cntRes
End Function
 
Upvote 0
Can you try this formula in C3
=CountCellsByFontColor(K1:O15,E3)
and see if it counts just the cells with green fonts correctly? I think the issue is you are trying to tell it to count by color, and also the name referenced, BUT the function can only count colors, the function formula pays no mind to the name.
 
Upvote 0
Anfinsen said:
Can you try this formula in C3
=CountCellsByFontColor(K1:O15,E3)
and see if it counts just the cells with green fonts correctly? I think the issue is you are trying to tell it to count by color, and also the name referenced, BUT the function can only count colors, the function formula pays no mind to the name.
Click to expand...
I get #NAME! in C3
 
Upvote 0
What do you get if you change the code to the code below (Btw I don't know why you get #NAME with =CountCellsByFontColor(K1:O15,E3), I got 4 [your formula only goes to row 15, not 16])

VBA Code: 
Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long, myName As String

    Application.Volatile
    cntRes = 0
    indRefColor = font_color.Cells(1, 1).Font.Color
    myName = Trim(font_color.Cells(1, 1).Value)
    For Each cellCurrent In data_range
        If indRefColor = cellCurrent.Font.Color And Trim(Right(cellCurrent.Value, Len(cellCurrent.Value) - InStr(cellCurrent.Value, "."))) = myName Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent

    CountCellsByFontColor = cntRes
End Function

Book2
ABCDEFGHIJKLMNO
1NUMBER OF*** Total Spins ***12345
2SpinsWinsPicksPlayer51. Austin Rowe1. Chris Leiter1. Stephen Gross1. Stephen Gross1. Stephen Gross
34215Stephen Gross2. Rick Conte2. Daniel Branco2. Daniel Branco2. Daniel Branco2. Stephen Gross
4Function Called3. Rick Conte3. Aaron Burris3. Calvin VanRiper3. Stephen Gross3. Stephen Gross
54. Shane Fair4. Shane Fair4. Shane Fair4. Shane Fair4. John Alger
65. Stephen Gross5. Chris Leiter5. Calvin VanRiper5. Stephen Gross5. Stephen Gross
76. Shane Fair6. Shane Fair6. Shane Fair6. Shane Fair6. Shane Fair
87. Tim and Kim Taylor7. Daniel Branco7. Calvin VanRiper7. Stephen Gross7. Stephen Gross
98. Randy Czapla8. Daniel Branco8. Daniel Branco8. Daniel Branco8. John Alger
109. Shane Fair9. Shane Fair9. Shane Fair9. Shane Fair9. Shane Fair
1110. Stephen Gross10. Sandie Smith10. Sandie Smith10. Sandie Smith10. Calvin VanRiper
1211. Lee Clyde11. Tim and Kim Taylor11. Stephen Gross11. Austin Rowe11. Calvin VanRiper
1312. Sandie Smith12. Daniel Branco12. Daniel Branco12. Daniel Branco12. Shane Fair
1413. Calvin VanRiper13. Lee Clyde13. John Alger13. Tim and13. Laura N
1514. Rick Conte14. Aaron Burris14. Stephen Gross14. Calvin VanRiper14. Laura N
1615. Calvin VanRiper15. Jason Easter15. Stephen Gross15. Calvin VanRiper15. Laura N
17Winning Number5112152
Sheet1
Cell Formulas
RangeFormula
J2J2=COUNT(K1:O1)
C3C3=CountCellsByFontColor(K1:O16,E3)
D3D3=COUNTIF(K2:O16, "*"&E3&"*")
 
Last edited:
Upvote 0
Solution
I commented out my Function and added yours and saved it, then added your formula to C3, what I get is #NAME? .
The cell format is NUMBER so that can't be it and the file itself is a XLSM.
 
Upvote 0
Discard my previous post I placed your function in the wrong workbook. Your function worked perfectly, I got the number 2 as should be in C3.

I really appreciate your help as this was driving me nuts for a couple of days sometimes it takes awhile to ask for help.

Thank you;
Jim
Problem solved.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
996
JamesCanale
JamesCanale
M
  • Question
Creating a daily census count
Replies
6
Views
7K
Peter_SSs
Peter_SSs
R
  • Question
Calculating combinations
Replies
6
Views
1K
Reboshua
R
D
  • Question
Formula for unique record count - do not count hidden records using autofilter
Replies
2
Views
696
deskjocky
D
D
  • Question
help on SUM
Replies
5
Views
6K
mvptomlinson
M

Forum statistics

Threads
1,215,069
Messages
6,122,956
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top