Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with criteria and sum
Hi -
I would like to know how to make certain cells one color, other cells another color, then sum those cells based on their color. Could someone assist me on how to do that? I'm using office 2007. thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with criteria and sum
You will not be able to do that with worksheet functions; will have to
develop a macro which does this using the color index. If this post helps click Yes --------------- Jacob Skaria "akantrow2" wrote: Hi - I would like to know how to make certain cells one color, other cells another color, then sum those cells based on their color. Could someone assist me on how to do that? I'm using office 2007. thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with criteria and sum
thank you, Jacob....do you know where on this discussion group i could find
additional information on how to do that? i'm a novice at Excel. Thanks so much! "Jacob Skaria" wrote: You will not be able to do that with worksheet functions; will have to develop a macro which does this using the color index. If this post helps click Yes --------------- Jacob Skaria "akantrow2" wrote: Hi - I would like to know how to make certain cells one color, other cells another color, then sum those cells based on their color. Could someone assist me on how to do that? I'm using office 2007. thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with criteria and sum
Try the below UDF. If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below function.Get back to Workbook. Function GetSumbyColor(varRange As Range, Optional varIDX As Long) If varIDX = 0 Then varIDX = -4142 For Each cell In varRange If cell.Interior.ColorIndex = varIDX Then GetSumbyColor = GetSumbyColor + cell.Value End If Next End Function Enter the below formula in any cell ;; A1:20 being the range with colored cells. The below user defined function will give you the sum for cells that are colored RED. in cell B1 to return sum of all RED colored cells =getsumbycolor(A1:A20,3) to return sum of all cells which are not colored =getsumbycolor(A1:A5,0) OR =getsumbycolor(A1:A5) Use the color index for more colors 1 = black 2 = white 3 = red 4 = green 5 = blue 6 = yellow and so on If this post helps click Yes --------------- Jacob Skaria "akantrow2" wrote: Hi - I would like to know how to make certain cells one color, other cells another color, then sum those cells based on their color. Could someone assist me on how to do that? I'm using office 2007. thank you! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with criteria and sum
Hi,
Don't give up quite yet... Please give us more details, for example do you want to color the cells based on their value and if so what are your condtions. Or are you coloring cell at random based on nothing. I think you can do what you want if you color coding is not random, but we need to understand your rules. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "akantrow2" wrote: Hi - I would like to know how to make certain cells one color, other cells another color, then sum those cells based on their color. Could someone assist me on how to do that? I'm using office 2007. thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
Count how many criteria in a column match criteria in another colu | Excel Discussion (Misc queries) | |||
EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |