Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Good Evening All,
I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Matthew,
Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Thanks Bernie,
No, no difference between the values except italicized/normal (font, size, colour all same), though could instruct the user tochange the font colour, (instead of using italics), if that would make things easier. I did not really get your point about using an extra column. Cheers for your prompt reply. I did think about using the colourfunction (which I like), but can I adapt that for 'colourfontfunction' ? Cheers Mathew "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Matthew, Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
My point about using the extra column is this: Say that you have values in column B, some of which
you want to count or sum. Instead of selecting, say, cell B10 and italicizing the cell, select cell C10 and enter an X. Then use the formulas =SUMIF(C1:C1000,"X",B1:B1000) =COUNTIF(C1:C1000,"X") You can use a VBA function: Function SumI(rR As Range) As Double Dim rC As Range SumI = 0 For Each rC In rR If rC.Font.Italic Then SumI = SumI + rC.Value Next rC End Function used like =SUMI(B2:B10) But chaning a font doesn't trigger a re-calc, so you would need to force a re-calc for this to work. HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Thanks Bernie, No, no difference between the values except italicized/normal (font, size, colour all same), though could instruct the user tochange the font colour, (instead of using italics), if that would make things easier. I did not really get your point about using an extra column. Cheers for your prompt reply. I did think about using the colourfunction (which I like), but can I adapt that for 'colourfontfunction' ? Cheers Mathew "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Matthew, Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Press Alt + F11 to open the Visual Basic Editor.
Click INSERT on the menu and select MODULE. In the new module enter the text between the lines of dashes: ------------------------------------------------------------------------------------------ Function SumItalics(ValueRange As Range) As Double Dim dblRetVal As Double Dim l As Long Application.Volatile For l = 1 To ValueRange.Cells.Count If ValueRange.Cells(l).Font.Italic = True Then dblRetVal = dblRetVal + ValueRange.Cells(l) End If Next l SumItalics = dblRetVal ------------------------------------------------------------------------------------------ To use it enter the function as you would any other function: =SumItalics(A1:A10) The press enter. Hope this helps. End Function -- Kevin Backmann "Mathew P Bennett" wrote: Thanks Bernie, No, no difference between the values except italicized/normal (font, size, colour all same), though could instruct the user tochange the font colour, (instead of using italics), if that would make things easier. I did not really get your point about using an extra column. Cheers for your prompt reply. I did think about using the colourfunction (which I like), but can I adapt that for 'colourfontfunction' ? Cheers Mathew "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Matthew, Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Thanks Kevin,
This a nice useful code & function. It works for wha I want. Hopefully I will be able to adapt it 'count' also. Cheers again Mathew "Kevin B" wrote in message ... Press Alt + F11 to open the Visual Basic Editor. Click INSERT on the menu and select MODULE. In the new module enter the text between the lines of dashes: ------------------------------------------------------------------------------------------ Function SumItalics(ValueRange As Range) As Double Dim dblRetVal As Double Dim l As Long Application.Volatile For l = 1 To ValueRange.Cells.Count If ValueRange.Cells(l).Font.Italic = True Then dblRetVal = dblRetVal + ValueRange.Cells(l) End If Next l SumItalics = dblRetVal ------------------------------------------------------------------------------------------ To use it enter the function as you would any other function: =SumItalics(A1:A10) The press enter. Hope this helps. End Function -- Kevin Backmann "Mathew P Bennett" wrote: Thanks Bernie, No, no difference between the values except italicized/normal (font, size, colour all same), though could instruct the user tochange the font colour, (instead of using italics), if that would make things easier. I did not really get your point about using an extra column. Cheers for your prompt reply. I did think about using the colourfunction (which I like), but can I adapt that for 'colourfontfunction' ? Cheers Mathew "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Matthew, Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Here's your count verion:
Function CountItalics(ValueRange As Range) As Double Dim lngItalics As Long Dim l As Long Application.Volatile For l = 1 To ValueRange.Cells.Count If ValueRange.Cells(l).Font.Italic = True Then lngItalics = lngItalics + 1 End If Next l CountItalics = lngItalics End Function -- Kevin Backmann "Mathew P Bennett" wrote: Thanks Kevin, This a nice useful code & function. It works for wha I want. Hopefully I will be able to adapt it 'count' also. Cheers again Mathew "Kevin B" wrote in message ... Press Alt + F11 to open the Visual Basic Editor. Click INSERT on the menu and select MODULE. In the new module enter the text between the lines of dashes: ------------------------------------------------------------------------------------------ Function SumItalics(ValueRange As Range) As Double Dim dblRetVal As Double Dim l As Long Application.Volatile For l = 1 To ValueRange.Cells.Count If ValueRange.Cells(l).Font.Italic = True Then dblRetVal = dblRetVal + ValueRange.Cells(l) End If Next l SumItalics = dblRetVal ------------------------------------------------------------------------------------------ To use it enter the function as you would any other function: =SumItalics(A1:A10) The press enter. Hope this helps. End Function -- Kevin Backmann "Mathew P Bennett" wrote: Thanks Bernie, No, no difference between the values except italicized/normal (font, size, colour all same), though could instruct the user tochange the font colour, (instead of using italics), if that would make things easier. I did not really get your point about using an extra column. Cheers for your prompt reply. I did think about using the colourfunction (which I like), but can I adapt that for 'colourfontfunction' ? Cheers Mathew "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Matthew, Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Italics
Kevin, Cheers, Most Appreciated.
Thank you Mathew "Kevin B" wrote in message ... Here's your count verion: Function CountItalics(ValueRange As Range) As Double Dim lngItalics As Long Dim l As Long Application.Volatile For l = 1 To ValueRange.Cells.Count If ValueRange.Cells(l).Font.Italic = True Then lngItalics = lngItalics + 1 End If Next l CountItalics = lngItalics End Function -- Kevin Backmann "Mathew P Bennett" wrote: Thanks Kevin, This a nice useful code & function. It works for wha I want. Hopefully I will be able to adapt it 'count' also. Cheers again Mathew "Kevin B" wrote in message ... Press Alt + F11 to open the Visual Basic Editor. Click INSERT on the menu and select MODULE. In the new module enter the text between the lines of dashes: ------------------------------------------------------------------------------------------ Function SumItalics(ValueRange As Range) As Double Dim dblRetVal As Double Dim l As Long Application.Volatile For l = 1 To ValueRange.Cells.Count If ValueRange.Cells(l).Font.Italic = True Then dblRetVal = dblRetVal + ValueRange.Cells(l) End If Next l SumItalics = dblRetVal ------------------------------------------------------------------------------------------ To use it enter the function as you would any other function: =SumItalics(A1:A10) The press enter. Hope this helps. End Function -- Kevin Backmann "Mathew P Bennett" wrote: Thanks Bernie, No, no difference between the values except italicized/normal (font, size, colour all same), though could instruct the user tochange the font colour, (instead of using italics), if that would make things easier. I did not really get your point about using an extra column. Cheers for your prompt reply. I did think about using the colourfunction (which I like), but can I adapt that for 'colourfontfunction' ? Cheers Mathew "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Matthew, Is there something about the italicized values that you can determine by other than visually looking at them, or checking their font? Are they all negative, or greater than 100, or.... Anyway, lacking logic to select those values, a far better option would be to use another column, and enter a value that could be checked, using a SUMIF / COUNTIF to get your values. Think of the person who will inherit this workbook..... HTH, Bernie MS Excel MVP "Mathew P Bennett" wrote in message ... Good Evening All, I have a ss (cash flow) where some of the values are entered as italics, and the rest as normal. I would like to be able to differenciate between the two when using a count function, plus using the sum function (but not together) I thought of using an array, something like this, (or maybe a sumproduct) ie SUM{(IF(A1:A10<Italics,FALSE,A1:10))} I feel sure some VBA will be needed. Any help or pointers would be most appreciated. Cheers Mathew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If cell is Bold then 1, if cell is Italics then 2? | Excel Discussion (Misc queries) | |||
can a function return part of it's result in italics? | Excel Worksheet Functions | |||
Summings cells in Italics | Excel Discussion (Misc queries) | |||
Stuck in Italics | Excel Discussion (Misc queries) | |||
bold italics or underline | Excel Discussion (Misc queries) |