sumproduct based on format

[mhoffmeier]

Is it possible to test a column of values based on their format? I'd
like to be able to use sumproduct on the italized numbers in a column.

 

[Bob Phillips]

You would need a UDF that calculates if italicized, and use that like this

=SUMPRODUCT(--(IsItalics(A1:A10)))

Here is the UDF

'---------------------------------------------------------------------
Function IsItalics(rng As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim ary As Variant

If rng.Areas.Count > 1 Then
IsItalics = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
ary = rng.Font.Italic
Else
ary = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
ary(i, j) = cell.Font.Italic
Next cell
Next row
End If

IsItalics = ary

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[mhoffmeier]

Thanks, I need to learn how to program these functions. It is amazing