• 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.

Sum of row by background color for positive or negative numbers

B

Blair

New Member
I have a matrix of cells in columns and rows. Each cell contains a number in percentage format - some positive and some negative. These numbers are entered manually as they originate from another worksheet. The cells all have a background color entered manually (currently only two: green and gray) indicating an action to be taken. With your help I’m now able to obtain a sum, by row, by background color. I’m also able to obtain the minimum value (the lowest single cell value in a row) by background color.


What I need to further enhance my work is a formula to calculate the following by row:

Sum of all green cells greater than zero in percentage format

Sum of all green cells less than zero in percentage format

Absolute value of all green cells in percentage format

Absolute value of all green cells less than zero in percentage format

Count of all green cells

Count of all green cells greater than zero


Thank you for your work in the past and in the future.


Blair
 
Hi Blair,


I am assuming you are using some sort of UDF to determine the background color.


Since you indicate that you already know how to sum by background color, you could use an AND condition to check the color, and whether the cell value meets criteria (such as less than zero, greater than zero)


Absolute value of cells can be determined using ABS function.


Once you get your formula results, you should be able to format the cells as percentage format.


For counting cells, use the COUNTIF or COUNTIFS function.


Please feel free to post the formulas that are giving you trouble, and we will be happy to correct them for you.


Cheers,

Sajan.
 
Hi Sajan

Thanks for your offer to help. Below is the VBA formula I'm using to sum my various color coded cells.

[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]

Following is the formula to identify the background color and give me the sum of the row:

=colorfunction($AC$1,$C6:I6,TRUE) True gives a sum and False gives the lowest single cell value in the row (the maximum drawdown).


Can you help me modify the formulas to sum only the positive values and only the negative values for a given background color? I also need a count of the cells with a positive value and a count of the cells with a negative value for a given background color.


Thank you.

Blair
 
Hi, Blair!


Trying to modify as slightly as possible the code you posted (next time embed it within bacticks [`] so as to keep indentation), this might be a solution:


-----

[pre]
Code: 
Option Explicit

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM_MIN As Boolean, Optional iSign As Integer)
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
If Sgn(rCell.Value) = iSign Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
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]
-----


The changes?

- new optional parameter iSign (<0 for negative and >0 for positive numbers)

- control against rCell.value for adding or not


Just advise if any issue.


Regards!
 
Hi SirJB7,

Thanks for the VBA correction.


I would imagine that a better UDF would be one that can return a true or false to indicate that the color matched or not. Then one could use the "normal" functions (like SUM, SUMPRODUCT, etc.) to sum, count, etc., making the UDF truly reusable in a variety of situations.


Since you are a VBA Ninja, perhaps you could supply such a function for folks to use!! :)


Regards,

Sajan.
 
@Sajan

You don't know how much I appreciate your words... do you?... I'm so happy... you wouldn't actually realize how much :'(

VBA Ninja but not magician yet I guess, as I don't have a clue of how to do such a thing.

Regards!
 
Hi SirJB7,

Perhaps you are thinking of something more elaborate for the UDF. I was suggesting something more reusable, such as the following:


CellColor(ColorToMatch, RangeToCheck)

should return boolean True / False, or if the RangeToCheck has multiple cells, return an array of True / False.


This would allow one to use a function like

=SUMPRODUCT(ColorMatch("Red", A2:A10) * B2:B10)


Of course, it may be better to point to another cell with the desired color, instead of the text "Red" for the first argument.


Hopefully, a function such as the one above would not require any magic!! :)


Regards,

Sajan.
 
@Sajan

Hi!

Yes and no. I was actually thinking about exactly that kind of UDF (2 versions, one for referencing another cell backcolor and another with the long value for the back color), but got stuck at the damned Interior.ColorIndex and code stopped and UDF didn't finished normally, no error but no end of run.

So sorry, but still required a touch of magic that I can't find out.

Regards!
 
Hi SirJB7,

Here is a simple UDF that returns an array of True/False to match the range being checked. It can then be used in other Excel functions such as SUMPRODUCT.

[pre]
Code: 
Function ColorMatch(SourceColorCell As Range, SearchRng As Range) As Variant
Dim MatchResultsArray() As Boolean
Dim SingleMatchResult As Boolean
Dim NumRows As Long
Dim NumCols As Long
Dim i As Integer
Dim j As Integer

NumRows = SearchRng.Rows.Count
NumCols = SearchRng.Columns.Count

If (NumRows = 1) And (NumCols = 1) Then
SingleMatchResult = (SearchRng.Cells(1, 1).Interior.ColorIndex = SourceColorCell.Cells(1, 1).Interior.ColorIndex)
ColorMatch = SingleMatchResult
Exit Function
End If

ReDim MatchResultsArray(1 To NumRows, 1 To NumCols)

For i = 1 To NumRows
For j = 1 To NumCols
If SearchRng.Cells(i, j).Interior.ColorIndex = SourceColorCell.Cells(1, 1).Interior.ColorIndex Then
MatchResultsArray(i, j) = True
Else
MatchResultsArray(i, j) = False
End If
Next j
Next i

ColorMatch = MatchResultsArray
End Function
[/pre]
One could then use it like:

=SUMPRODUCT(ColorMatch(A1, B1:B10)*C1:C10)

to sum the range C1:C10 where B1:B10 has the same color as A1


Regards,

Sajan.
 
@Sajan

Hi!

Good job, man. Didn't test it but sure it'll work fine. Thanks for doing my job :p

Regards!
 
I think the suggestion of having an array friendly UDF to return TRUE/FALSE is a good one: it certainly gives flexibility on further calculations. It reminded me of an old thread I posted on at MrExcel.


However, I think it's worth mentioning that, in my opinion, it's not a good idea to sum etc by cell colour. The main issue is that changing the colour of a cell does not trigger a calculation event, so the formula result will be out of date until it is forced to recalculate. A better alternative is to derive the logic as to why a cell is coloured a particular way and to impose that logic within the formula.


That said, a couple of small improvements which could be made to the UDF colour solution would be:

(1) Make the UDF volatile so it will at least recalculate on the next calculation event

(2) Change the i variable to a Long type to avoid a potential overflow error. In fact, I'd change the j variable to a Long type too, but that's another story. :)
 
Thanks Colin.


Since VBA is not my strength, I figured others will improve it.


Thanks for the warning regarding cell color changes not triggering recalculation in the UDF. One more reason to stick with formulas! I am personally not a fan of worksheets that rely on color alone to indicate criteria or significance, since anything but primary colors is often difficult to differentiate. This was merely an exercise to explore / illustrate the possibility.

(I read your old thread on Mr. Excel just now. Good to know that I was not too far off!)


And regarding the i and j variables... oh well! I tried...! :)


Please feel free to post a modified / improved UDF. While not endorsing the approach, folks may still find it useful.


Regards,

-Sajan.
 
Hi Sajan,


Sure, here are the modifications I suggested -

[pre]
Code: 
Public Function ColorMatch(ByRef SourceColorCell As Range, ByRef SearchRng As Range) As Variant

Dim MatchResultsArray() As Boolean
Dim NumRows As Long, NumCols As Long
Dim i As Long, j As Long
Dim ColIndex As Variant

Application.Volatile

NumRows = SearchRng.Rows.Count
NumCols = SearchRng.Columns.Count

ReDim MatchResultsArray(1 To NumRows, 1 To NumCols)

ColIndex = SourceColorCell.Cells(1, 1).Interior.ColorIndex

For i = 1 To NumRows
For j = 1 To NumCols
MatchResultsArray(i, j) = _
(SearchRng.Cells(i, j).Interior.ColorIndex = ColIndex)
Next j
Next i

ColorMatch = MatchResultsArray

End Function
[/pre]

Now it's my bedtime! :)
 
Hi SirJB7

A computer crash and new computer have kept me from working with your latest Formula – copy shown below:

Option Explicit

[pre]
Code: 
[Function ColorFunction(rColor As Range, rRange As Range, Optional SUM_MIN As Boolean, Optional iSign As Integer)
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
If Sgn(rCell.Value) = iSign Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
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]

You added a new parameter iSign which looks like it will work. Please help me understand how to modify my ColorFunction formula to get just the sum of the positive numbers and the sum of the negative numbers in a given row. From those two totals we can compute the absolute sum of the row.

Would also like to get a count of the items that are positive and a count of the items that are negative to compute a percentage.

Thanks again for your help,


Blair
 
Hi SirJB7


I had a couple of questions related to your help several weeks ago. Please see my post of four days ago (Thursday, December 13, 2012.


Thanks

Blair
 
Blair


We receive between 20 and 30 new posts a day and so finding a specific post from 4 days ago can be a challenge


You should post reminders directly in the post and then the acual post is brought to the front of the list again
 
Hui

I don't understand what you mean by "post reminders directly in the post". Should I send my post again?


Blair
 
Blair


My mistake


I thought you were referring to a Different Post, but your referring to this post.


Hui...
 
Will you, or someone else be able to help me? It's bedtime. I'll check tomorrow.


Thank you very much.


Blair
 
Blair


I'd change the code slightly as below:

[pre]
Code: 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM_MIN As Boolean, Optional iSign As Integer = 0)
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
'Modified by Hui
'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
If iSign = -1 And rCell.Value < 0 Then
vResult = vResult + rCell.Value
ElseIf iSign = 1 And rCell.Value > 0 Then
vResult = vResult + rCell.Value
ElseIf iSign = 0 Then
vResult = vResult + rCell.Value
End If
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 And min_val > rCell Then min_val = rCell
Next rCell
vResult = min_val
End If
ColorFunction = vResult
End Function
[/pre]

Then in use


=ColorFunction(Color Match Cell, Cells to Sum, Min/Sum , iSign)


Color Match Cell = Cell with a color which you want to match

Cells to Sum = Your range you want to sum/min

Min/Sum: True = Sum or False = Min

iSign: -1 Sum Negatives, 1 Sum positives, 0 or missing Sum all


in use:


=ColorFunction(B9,D1:M1,1,-1) ' Sum negatives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1,1) ' Sum positives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1,0) ' Sum all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1) ' Sum all cells between D1:M1 that match color in B9
 
Thanks Hui

The formula from yesterday works just fine. To complete my project I need to be able to get a COUNT of all the positive cells of a given color and a COUNT of all the negative cells of the same color. This will allow me to compute percentages of wins and/or losses.


BTY - the last two ColorFunctions you gave me produce the same results. Is this what you intended?


Blair
 
[pre]
Code: 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM_MIN As Boolean = True, Optional iSign As Integer = 0)
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
'Modified by Hui
'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. = Default Value
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
If iSign = -1 And rCell.Value < 0 Then
vResult = vResult + rCell.Value
ElseIf iSign = 1 And rCell.Value > 0 Then
vResult = vResult + rCell.Value
ElseIf iSign = 0 Then
vResult = vResult + rCell.Value
End If
End If
Next rCell
Else ' SUM_MIN is false implies that the minimum of the cells is to be returned.
vResult = 0
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
If iSign = -1 And rCell.Value < 0 Then
vResult = vResult + 1
ElseIf iSign = 1 And rCell.Value > 0 Then
vResult = vResult + 1
ElseIf iSign = 0 Then
vResult = vResult + 1
End If
End If
Next rCell

End If
ColorFunction = vResult
End Function
[/pre]

In use:

=ColorFunction(B9,D1:M1, False,-1) ' Count negatives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1, False,1) ' Count positives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1, False,0) ' Count all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,False) ' Count all cells between D1:M1 that match color in B9


I have also set it so that the two optional parameters, default to Sum and All

that is :

=ColorFunction(B9,D1:M1,,) is the same as =ColorFunction(B9,D1:M1,True,0)

which is ' Sum all cells between D1:M1 that match color in B9
 
We are so close that I can taste success! The count functions work as requested. The changes you made yesterday have removed the "MINs if SUM_MIN is false" function. Will you please restore this function? Then I think we are through. I can't tell you how valuable your work is to me. Thank you.


Blair
 
Blair


Have restored this and added a Maximum value as well

[pre]
Code: 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM_MIN As Integer = 0, Optional iSign As Integer = 0)
Dim rCell As Range
Dim lCol As Long
Dim vResult
Dim min_val As Variant
min_val = 1E+16 ' An arbitrary high-value

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Modified by Hui
'SUM_Min
'0 - Sum cells
'1 - Count
'2 - Minimum
'3 - Maximum
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM_MIN = 0 Then ' Sum cells. = Default Value
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
If iSign = -1 And rCell.Value < 0 Then
vResult = vResult + rCell.Value
ElseIf iSign = 1 And rCell.Value > 0 Then
vResult = vResult + rCell.Value
ElseIf iSign = 0 Then
vResult = vResult + rCell.Value
End If
End If
Next rCell
ElseIf SUM_MIN = 1 Then ' Count cells.
vResult = 0
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
If iSign = -1 And rCell.Value < 0 Then
vResult = vResult + 1
ElseIf iSign = 1 And rCell.Value > 0 Then
vResult = vResult + 1
ElseIf iSign = 0 Then
vResult = vResult + 1
End If
End If
Next rCell
ElseIf SUM_MIN = 2 Then ' extract Minimum
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
If iSign = -1 And rCell.Value < 0 And rCell < min_val Then
min_val = rCell
ElseIf iSign = 1 And rCell.Value >= 0 And rCell < min_val Then
min_val = rCell
ElseIf iSign = 0 And rCell < min_val Then
min_val = rCell
End If
End If
Next rCell
vResult = min_val

ElseIf SUM_MIN = 3 Then ' extract Maximum
min_val = -min_val
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
If iSign = -1 And rCell.Value < 0 And rCell.Value > min_val Then
min_val = rCell
ElseIf iSign = 1 And rCell.Value >= 0 And rCell > min_val Then
min_val = rCell
ElseIf iSign = 0 And rCell > min_val Then
min_val = rCell
End If
End If
Next rCell
vResult = min_val

End If
ColorFunction = vResult
End Function
[/pre]

in use:


=ColorFunction(Color Match Cell, Cells to Sum, Func , iSign)


Color Match Cell = Cell with a color which you want to match

Cells to Sum = Your range you want to sum/min

Func: =0 Sum values (Default)

=1 Count

=2 Minumum

=3 Maximum


iSign: -1 Sum Negatives, 1 Sum positives, 0 or missing Sum all


in use:

=ColorFunction(B9,D1:M1,0,-1) ' Sum negatives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,0,1) ' Sum positives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,0,0) ' Sum all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,,) ' Sum all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1) ' Sum all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1,-1) ' Count negatives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1,1) ' Count positives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1,0) ' Count all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,1) ' Count all cells between D1:M1 that match color in B9


=ColorFunction(B9,D1:M1,2,-1) ' Minimum of all negatives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,2,1) ' Minimum of all positives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,2,0) ' Minimum of all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,2) ' Minimum of all cells between D1:M1 that match color in B9


=ColorFunction(B9,D1:M1,3,-1) ' Maximum of all negatives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,3,1) ' Maximum of all positives between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,3,0) ' Maximum of all cells between D1:M1 that match color in B9

=ColorFunction(B9,D1:M1,3) ' Maximum of all cells between D1:M1 that match color in B9
 
You must log in or register to reply here.
Back
Top