Forum Discussion
Merge SUMIF with SUMBYCOLOR
I am desperately looking for help with this.
i have a sheet with a working sumbycolor macro to sum a range if a different range is of a certain colour.
it also has a working sumif formula to sum a range if a different range contains a specific word.
What i am trying to now do is create a cell that contains both.
I have tried using different combinations of sumif sumifs if and sumbycolor formulae with no success
EG
i am wanting a cell to have as its final value the sum of a column of numerical amounts, as long as the rows contain a certain word and are coloured specifically (both separate columns).
See attached image.
I require the total of column D for each "name" separated by color red, orange and green.
So that in "name1" box the red cells in D that correlate to "name1" in F are totaled in the red box.
I really hope i havnt over/under explained this.
*edit site will not allow me to uplaod a macro-enabled worksheet .xlsm
*edit added UDF code for SUMBYCOLOR:
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
7 Replies
Hello,
what you describe can probably be achieved by adding a condition to the SumByColour UDF. If you post the code of the macro, we can work it out.
I dont know enough about all this, especially UDF, but wont editing the code change the way the whole thing works?
Whereas i am looking for something that affects only certain cells, and in slightly different ways at that.
Either way, thank you for looking into it, it's driving me mad for the last three months!
My regular reply to requests to sum or count by colour is that colour is not data, but metadata and is hard to capture in an Excel formula.
The UDF loops through the cells and adds each cell that fulfills the colour condition. In order to add another condition that condition must be in the UDF with AND logic, i.e. if it matches the colour AND matches the name. It's not possible to do that any other way if you want to keep using the UDF.
A much better data architecture would be to have a status column in your source table with hard data about each row. Let it be "red", "yellow", "green" or "open", "in progress", "overdue" or whatever. Then you can use conditional formatting for the colouring, and you can use a simple Sumifs() formula to calculate the status-specific total by name.
The basis here is that colour is the decoration that is based on hard data, and it is difficult to turn decoration into data that can be used in worksheet formulas.
Having said all that, you can change the UDF so it accommodates a new condition and also can be used with the new condition or without, so it does not break the existing formulas.
Your screenshot is from your question in Excelforum. These guys will do anything in VBA. What answer did you get there? Do you need an answer here at all anymore? Are we just wasting our time trying to help you with something that you already have a solution for?
Please don't post the same question in more than one online forum.
