Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct based on format
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. |
#2
|
|||
|
|||
sumproduct based on format
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" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct based on format
Thanks, I need to learn how to program these functions. It is amazing
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does my format keep changing in the cell? | Excel Discussion (Misc queries) | |||
How do I change the format of a cell based on what I input? | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
protecting format | Excel Discussion (Misc queries) | |||
formula based on format | Excel Discussion (Misc queries) |