Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count the number of Excel cells with text formatted Italic
I have a large spreadsheet in which I am trying to calculate the number cells
in each row with specific contents. Using the "COUNTIF()" function, I am able to do almost all of what I want. However, I need to subtract out any cells that have the contents formatted in Italic text. I cannot find any way to do so. Any suggestions would be welcome. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count the number of Excel cells with text formatted Italic
The only way you can do this is by creating a UDF:
1) Alt+F11 to get to the VBA editor 2) Insert a module (right click on your sheet name and choose <Insert<Module) 3) Paste this code: Function CountItalic(rng As Range) Count = 0 For Each rng In rng If rng.Font.Italic = True Then Count = Count + 1 End If Next CountItalic = Count End Function Then you will be able to use the function CountItalic() in your worksheet. Does that help? -- Regards, Dave "phausman" wrote: I have a large spreadsheet in which I am trying to calculate the number cells in each row with specific contents. Using the "COUNTIF()" function, I am able to do almost all of what I want. However, I need to subtract out any cells that have the contents formatted in Italic text. I cannot find any way to do so. Any suggestions would be welcome. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count the number of Excel cells with text formatted Italic
phausman wrote: I have a large spreadsheet in which I am trying to calculate the number cells in each row with specific contents. Using the "COUNTIF()" function, I am able to do almost all of what I want. However, I need to subtract out any cells that have the contents formatted in Italic text. I cannot find any way to do so. Any suggestions would be welcome. Hi phausman: Further to David's submission, be aware that format changes do not trigger sheet calculations, so you'll have to re-enter the formula (just activate the formula's cell, click in the formula bar and press enter) or enter a value in the range to be checked. Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count the number of Excel cells with text formatted Ita
Got me exactly where I needed to be. Thanks.
To make sure I did not count blank italic-formatted cells, I did have to add a nested if with the resultant formula: 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 The only way you can do this is by creating a UDF: 1) Alt+F11 to get to the VBA editor 2) Insert a module (right click on your sheet name and choose <Insert<Module) 3) Paste this code: Function CountItalic(rng As Range) Count = 0 For Each rng In rng If rng.Font.Italic = True Then Count = Count + 1 End If Next CountItalic = Count End Function Then you will be able to use the function CountItalic() in your worksheet. Does that help? -- Regards, Dave "phausman" wrote: I have a large spreadsheet in which I am trying to calculate the number cells in each row with specific contents. Using the "COUNTIF()" function, I am able to do almost all of what I want. However, I need to subtract out any cells that have the contents formatted in Italic text. I cannot find any way to do so. Any suggestions would be welcome. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count cells with text in them? | Excel Discussion (Misc queries) | |||
How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
Excel cells should take number format and/or optional text eg Dec. | Excel Discussion (Misc queries) | |||
Access Excel Linked Text and Number Issues | Excel Discussion (Misc queries) | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) |