If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting by color
I saw this formula on Chip Pearson's site. According to Chip:
You can use the ColorIndexOfRange function to get the sum of the values in those cells whose color index is some specified value. For example, the following array formula will sum the values of the cells in range B11:B17 whose fill color is red. =SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3 )) I tried to use this formula in a spreadsheet, but received a#NAME? error. I'm trying to count all occurences in a range of cells that are in red font, but I'm not having much luck. |
#2
|
|||
|
|||
Counting by color
The ColorIndexOfRange function doesn't come with Excel; it's a user-defined
function (UDF) written by Chip Pearson. You have have to add it to each workbook where you want to use it. The code is written in Visual Basic for Applications (VBA) for Excel, and is given earlier on the same page as your quote. Jon Peltier's site has good instructions on how to add any UDF or macro to your workbook: http://peltiertech.com/WordPress/200...e-elses-macro/ Hope this helps, Hutch "Danny Boy" wrote: I saw this formula on Chip Pearson's site. According to Chip: You can use the ColorIndexOfRange function to get the sum of the values in those cells whose color index is some specified value. For example, the following array formula will sum the values of the cells in range B11:B17 whose fill color is red. =SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3 )) I tried to use this formula in a spreadsheet, but received a#NAME? error. I'm trying to count all occurences in a range of cells that are in red font, but I'm not having much luck. |
#3
|
|||
|
|||
Counting by color
Try this garage version. You will have to name the range you want to count
the fonts in to Data. (or any name you wish, but include THAT name in the code) This counts three colors and you may want to omit (delete from the code) those you don't want. I would add, it seems I remember Chip's site also offers a font color count solution. His, along with his excellent narrative at the site would surely be a better choice. Sub FontColorCount() 'Counts the number of colored 'fonts in a range named Data. Dim cell As Range Dim Blue5 As Integer, Red3 As Integer, _ Green4 As Integer, Yellow6 As Integer For Each cell In Range("Data") If cell.Value "" _ And cell.Font.ColorIndex = 5 Then Blue5 = Blue5 + 1 ElseIf cell.Value "" _ And cell.Font.ColorIndex = 3 Then Red3 = Red3 + 1 ElseIf cell.Value "" _ And cell.Font.ColorIndex = 4 Then Green4 = Green4 + 1 ElseIf cell.Value "" _ And cell.Font.ColorIndex = 6 Then Yellow6 = Yellow6 + 1 End If Next Range("A1").Value = Blue5 & " Blue" Range("A2").Value = Red3 & " Red" Range("A3").Value = Green4 & " Green" Range("A4").Value = Yellow6 & " Yellow" MsgBox " You have: " & vbCr _ & vbCr & " Blue " & Blue5 _ & vbCr & " Red " & Red3 _ & vbCr & " Green " & Green4 _ & vbCr & " Yellow " & Yellow6, _ vbOKOnly, "CountColor" End Sub HTH Regards, Howard "Danny Boy" wrote in message news I saw this formula on Chip Pearson's site. According to Chip: You can use the ColorIndexOfRange function to get the sum of the values in those cells whose color index is some specified value. For example, the following array formula will sum the values of the cells in range B11:B17 whose fill color is red. =SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3 )) I tried to use this formula in a spreadsheet, but received a#NAME? error. I'm trying to count all occurences in a range of cells that are in red font, but I'm not having much luck. |
#4
|
|||
|
|||
Counting by color
In addition to the other replies.
How are the cells colored? Manually or CF? If by Conditional Formatting, colorindex is not recognized and you need a whole bunch more code from Chip's site. http://www.cpearson.com/excel/CFColors.htm Gord Dibben MS Excel MVP On Thu, 22 Apr 2010 11:23:01 -0700, Danny Boy wrote: I saw this formula on Chip Pearson's site. According to Chip: You can use the ColorIndexOfRange function to get the sum of the values in those cells whose color index is some specified value. For example, the following array formula will sum the values of the cells in range B11:B17 whose fill color is red. =SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)= 3)) I tried to use this formula in a spreadsheet, but received a#NAME? error. I'm trying to count all occurences in a range of cells that are in red font, but I'm not having much luck. |
Thread Tools | |
Display Modes | |
|
|