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

 

[njimack]

There were a couple of typos. Try this:
Function CountItalic(Rng As Range, searchtext As Range)
Dim C As Range
Dim count As Long
count = 0
For Each C In Rng
If C.Font.Italic = True And C.Value = searchtext Then count = count + 1
Next C
CountItalic = count
End Function

 

[Peterfc2]

There were a couple of typos. Try this:
Function CountItalic(Rng As Range, searchtext As Range)
Dim C As Range
Dim count As Long
count = 0
For Each C In Rng
If C.Font.Italic = True And C.Value = searchtext Then count = count + 1
Next C
CountItalic = count
End Function

=CountItalic(U4:W60,"Walker")

is this correct

 

[MARK858]

Try making the change in red.

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

 

[CharlesChuckieCharles]

okay this handles multi sheet ranges (not sure why the union wouldn't work , but i don't think CountIf works with "3D" Ranges anyway)

=FindWithItalics(A2:A3,"JJ",FALSE) 'dont include JJ in the count

=FindWithItalics(A2:A3,"JJ") ' include JJ in count

Changes to your data as follows : list sheet names and corresponding Ranges required (ranges don't have to match)

Col A Col B
1
2 Sheet2 U4:W60
3 Sheet3 U4:W60
4 MySheet Z45:AX1024


=FindWithItalics(
A2:A3, range of sheet names to search
"JJ", String sought
FALSE) include italic versions? False is No, Blank or TRUE is YES


Code for FindWithItalics

Function FindWithItalics(vsheets As Range, 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 TotalFound As Long
On Error Resume Next
For Each MySheet In vsheets
        
        Set CheckRange = Range(MySheet & "!" & MySheet.Offset(0, 1).Value)
        lCt = Application.WorksheetFunction.CountIf(CheckRange, sFindString)
        TotalFound = TotalFound + lCt
    
    'count italic
    If lCt <> 0 And bWithItalics = False Then
        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
                    End If
                    Set rFindRange = .FindNext(rFindRange)
                Loop While Not rFindRange Is Nothing And rFindRange.Address <> firstAddress
            End If
          TotalFound = TotalFound - lCt  'negate the Italicised
        End With
    End If
Next MySheet
FindWithItalics = TotalFound
End Function

 

[Peterfc2]

Try making the change in red.

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

Works fine

=CountItalic(U4:W60,"Walker")

Changed to

=CountItalic(U4:W60,c2)

works with Range instead of string. Solution is ok. Cheers and thank you

 

[CharlesChuckieCharles]

So no need to count multiple sheets?

 

[Rick Rothstein]

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

Three questions...

1) Will the name you are looking for be the only text in the cell or could there be other text in the cell along with the name?

2) How did the names get italicized... manually by a user or by means of Conditional Formatting?

3) Does the cell with the name contain a text constant or a formula that outputs the displayed text?