Italics problem

[Peterfc2]

I want to count a name match in multiple sheets (range is the same) but don't want it to count the names that are in italics. Count Smith but not Smith

Using this at the moment..

 =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!J4:w60"),C2))

a2:a4 are the names of the sheets etc.
j4:w60 is my range on each sheet
c2 is my text search. i.e. Smith

Any ideas to avoid it counting the text in italics please

 

[CharlesChuckieCharles]

Maybe

 

[Peterfc2]

Spoil me?

Maybe

 

[njimack]

Are you happy with a VBA approach. The only way I can think of doing this would involve a UDF.

 

[Peterfc2]

I will try any solution please

 

[CharlesChuckieCharles]

The Italic settting is no available through the sheet so would have to be a udf

But my code is very big and messy and not working with my Union

Function FindWithItalics(vsheets As Range, rRanges As String, sFindString As String, Optional bWithItalics = True) As Long
Dim lCt As Long
Dim rFindRange As Range
Dim firstAddress As Variant
Dim CheckRange As Range
Dim MySheet As Range
Dim deadrange As Range
Dim boolfirst As Boolean
On Error Resume Next
boolfirst = True
For Each MySheet In vsheets
    If boolfirst Then
        
        Set CheckRange = Range(MySheet.Value)
        boolfirst = False
    Else
        Set CheckRange = Application.Union(CheckRange, MySheet.Value)
    
    End If
Next MySheet
lCt = Application.WorksheetFunction.CountIf(CheckRange, sFindString)
If lCt = 0 Then
  FindWithItalics = 0
  Exit Function
End If
If bWithItalics Then
  FindWithItalics = lCt
  Exit Function
End If
'copied from the help system
lCt = 0
With CheckRange
    Set rFindRange = .Find(sFindString, LookIn:=xlValues)
    If Not rFindRange Is Nothing Then
        Debug.Print rFindRange.Worksheet.Name & ":" & rFindRange.Address
        firstAddress = rFindRange.Address
        Do
            If bWithItalics = False Then
            
                If rFindRange.Font.Italic = False Then
                
                    lCt = lCt + 1
                End If
            
            Else
               lCt = lCt + 1
            End If
            
            Set rFindRange = .FindNext(rFindRange)
        Loop While Not rFindRange Is Nothing And rFindRange.Address <> firstAddress
    End If
End With
FindWithItalics = lCt
CheckRange.Interior.ColorIndex = vbRed
End Function

 

[Peterfc2]

far to complex for me mate

 

[Peterfc2]

found this udf

Function CountItalic(rng As Range)
Count = 0
For Each rng In rng
If rng.Font.Italic = True Then
If rng.Value <> "" Then
Count = Count + 1
End If
End If
Next
CountItalic = Count
End Function

Can it be modified it to a countif? e.g. countIFitalic(range, search). like, countIFitalic(u4:w60,c2)

 

[njimack]

This modified version should work (untested)...

Function CountItalic(rng As Range, searchtext As Range)
Dim c As Range
Dim count As Long
count = 0
For Each c In rng
    If rng.Font.Italic = True And rng.Value = searchtext Then count = count + 1
Next c
CountItalic = count
End Function

 

[Peterfc2]

This modified version should work (untested)...

Function CountItalic(rng As Range, searchtext As Range)
Dim c As Range
Dim count As Long
count = 0
For Each c In rng
    If rng.Font.Italic = True And rng.Value = searchtext Then count = count + 1
Next c
CountItalic = count
End Function

Can someone test this i can't get it to work please