Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use the SumIF function with a criteria of a cell color?
Hi,
I am trying to use the SumIF function and use a criteria based on the color of the cells. I see I can sort and filter based on cell color, but cannot find how to add based on the cell color. Is this available, or do I need to wait for Microsoft to create a =color() function? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use the SumIF function with a criteria of a cell color?
Are the cells just randomly colored? Or are they conditionally formatted to
be a specific color based on certain conditions. If the latter, then you could do a SUMIF based on the same conditions. -- John C "Infernoo1" wrote: Hi, I am trying to use the SumIF function and use a criteria based on the color of the cells. I see I can sort and filter based on cell color, but cannot find how to add based on the cell color. Is this available, or do I need to wait for Microsoft to create a =color() function? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use the SumIF function with a criteria of a cell color?
Microsoft are not going to create a Volour fuynction, so write your own.
Function SumByColour(rng As Range, ci) Dim cnt As Long Dim cell As Range Application.Volatile For Each cell In rng If cell.Interior.ColorIndex = ci Then cnt = cnt + 1 End If Next cell SumByColour = cnt End Function and in you worksheet =SumByColour(A1:A10,3) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Infernoo1" wrote in message ... Hi, I am trying to use the SumIF function and use a criteria based on the color of the cells. I see I can sort and filter based on cell color, but cannot find how to add based on the cell color. Is this available, or do I need to wait for Microsoft to create a =color() function? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use the SumIF function with a criteria of a cell colo
I think this will give you what you need:
Function CountByColor(InRange As Range, _ WhatColorIndex As Integer, _ Optional OfText As Boolean = False) As Long ' ' This function return the number of cells in InRange with ' a background color, or if OfText is True a font color, ' equal to WhatColorIndex. ' Dim rng As Range Application.Volatile True For Each rng In InRange.Cells If OfText = True Then CountByColor = CountByColor - _ (rng.Font.ColorIndex = WhatColorIndex) Else CountByColor = CountByColor - _ (rng.Interior.ColorIndex = WhatColorIndex) End If Next rng End Function Regards, Ryan--- -- RyGuy "Bob Phillips" wrote: Microsoft are not going to create a Volour fuynction, so write your own. Function SumByColour(rng As Range, ci) Dim cnt As Long Dim cell As Range Application.Volatile For Each cell In rng If cell.Interior.ColorIndex = ci Then cnt = cnt + 1 End If Next cell SumByColour = cnt End Function and in you worksheet =SumByColour(A1:A10,3) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Infernoo1" wrote in message ... Hi, I am trying to use the SumIF function and use a criteria based on the color of the cells. I see I can sort and filter based on cell color, but cannot find how to add based on the cell color. Is this available, or do I need to wait for Microsoft to create a =color() function? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF function criteria options | Excel Worksheet Functions | |||
sumif, criteria, fill color | Excel Discussion (Misc queries) | |||
Can I use two criteria in the SUMIF worksheet function? | Excel Discussion (Misc queries) | |||
Help with SUMIF function criteria | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |