• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I use conditional formatting to solve my problem?

B

Blair

New Member
I have a table containing several columns and several rows of numerical data. Some cells are highlighted in either a blue or gray background color (fill) and the other cells have no background color (fill).


I would like to calculate the sum of each row: 1. Sum of cells not highlighted,

2. Sum of cells highlighted in blue,

3. Sum of cells highlighted in gray.


Is it possible to do this using conditional formatting or some other Excel function?
 
No, there is no native way to do this. Perhaps there's some condition that we could build a formula on as to why the cells are colored the way they are? If it's truly arbitrary, your best luck is to use an UDF.

http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
 
Thanks for your help. It'll take me a while to try this out but I'm sure it will do what I want.


Thanks again

Blair
 
Hi, Blair!

You can do it with a helper column, if you have a reduced number of backcolors as in this case (3).

Let's assume that your data is column A, that you can make column B to be the helper.

Try this:

a) set autofilter at least to column A

b) filter by color, without color

c) type a 0 (zero) in first filtered cell and copy down

d) repeat b) and c) for each next color assigning values 1, 2, and so on

e) remove filtering, show all rows

f) if you want the sum by colors let's say in C1, D1, and E1, in C1 type this and copy across:

=SUMAR.SI($B:$B;COLUMNA()-3;$A:$A) -----> in english: =SUMIF($B:$B,COLUMN()-3,$A:$A)


If you have a lot of rows this will save you a lot of time.


Here's a sample file:

http://dl.dropbox.com/u/60558749/Can%20I%20use%20conditional%20formatting%20to%20solve%20my%20problem_%20%28for%20Blair%20at%20chandoo.org%29.xlsx


Regards!
 
Blair

Also have a read of this Chandoo.org Forum post: http://chandoo.org/forums/topic/calculate-data-based-on-cell-color
 
Luke

Thanks for your UDF formula in answer to my question about calculating the sum of color coded cells in a row. What I didn't tell you was that the color codes I use are determined and inserted manually, not by formula. Your formula does just what I wanted.

You warned me that changing the color codes would not automatically update the formuals and in fact would upset the whole apple cart. You also offered some help in correcting that problem. I tried your "work-arounds" but have not been able to make them work. The only success I've had is deleting the whole worksheet and starting over from scratch. I'm trying to do "what-ifs" and it is necessary to change or delete the color codes Can you please offer me some more help that will allow me to change my color codes without destroying the validity of these formulas on the worksheet?


Blair
 
Hi Blair ,


I would like to put in a few words ; the UDF whose link Luke has posted is having the following description :


QUOTE


You can now use the custom function (ColorFunction) like;


=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1.


UNQUOTE


So , you can change your colours to your heart's content ; all that the UDF is looking at is whether there are any cells in the given range , which are coloured the same colour as the cell mentioned in the first parameter , in this example $C$1.


If you change your preferred colours from BLUE and GRAY to GREEN and YELLOW , just change the colours in the two reference cells from BLUE to GREEN , and from GRAY to YELLOW. Your formulae will not be affected in any way.


Narayan
 
Is it possible to use a "min" or "max" formula with the UDF you gave me to sum color coded cells?


Blair
 
With your help I'm now able to calculate the sum of cells based on the background color of the cell. I would now like to determine the maximum and minimum values for each cell color. Possible?
 
Hi Blair ,


Sorry for the delayed reply.


Replace the existing finction by the following code :

[pre]
Code: 
Function ColorFunction(rColor As Range, rRange As Range, Optional MAXMIN As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

Dim max_val As Variant

Dim min_val As Variant

max_val = -999999999999999#   ' An arbitrary low-value ; replace this by the Excel limit , if you want

min_val = 1E+16               ' An arbitrary high-value ; replace this by the Excel limit , if you want

''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com

'MAXMINs or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

lCol = rColor.Interior.ColorIndex

If MAXMIN = True Then

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

If max_val < rCell Then max_val = rCell

End If

Next rCell

vResult = max_val

Else

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

If min_val > rCell Then min_val = rCell

End If

Next rCell

vResult = min_val

End If

ColorFunction = vResult

End Function
[/pre]
I assume that you want MAX and MIN instead of SUM and COUNT ; in the above code , using the function with TRUE for the third parameter will return the maximum value , using it with FALSE will return the minimum value. If you want all four functions then we will have to modify the code a bit. Let me know.


Narayan
 
Narayan


We don't need the COUNT or MAX. We just need SUM and MIN. Can't imagine any other functions that would improve our worksheet. You have been a wonderful help. Thanks.


Blair
 
Hi Blair ,


The following code will return the SUM if the third parameter is TRUE , and the MIN if it is FALSE :

[pre]
Code: 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM_MIN As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

Dim min_val As Variant

min_val = 1E+16               ' An arbitrary high-value ; replace this by the Excel limit , if you want

''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com

'SUMs if SUM_MIN is true ; MINs if SUM_MIN is false.

'''''''''''''''''''''''''''''''''''''''

lCol = rColor.Interior.ColorIndex

If SUM_MIN Then     '  SUM_MIN is true implies that the cells are to be summed.

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.Sum(rCell, vResult)

End If

Next rCell

Else                '  SUM_MIN is false implies that the minimum of the cells is to be returned.

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

If min_val > rCell Then min_val = rCell

End If

Next rCell

vResult = min_val

End If

ColorFunction = vResult

End Function
[/pre]

Narayan
 
You must log in or register to reply here.
Back
Top