Forum Discussion
Merge SUMIF with SUMBYCOLOR
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.
Thank you for your reply.
If it can be done by editing the UDF, that would be amazing. From your explanation it looks like that would be the only way.
I am most certainly not wasting your time. I posted here because I received no answer there and have waited many weeks.
Honestly, why don't you just add a column to the data and use conditional formatting? That is so much easier, will work without VBA and in any workbook, online and on the phone.
Writing VBA to overcome the flaws of the data architecture is really the last resort.
Where is the data with the names in relation to the data with the colors? In your screenshot it's two columns to the right. Will that be assumed as a fixed position? Or will it vary?
The data will always be in the same columns if thats what you mean?
the names in one.
the colours/amounts to sum in another.
whether the location remains fixed depends on if i add or edit the document in the future.
would that matter? i thought excel automatically updates formulae when rows/columns/cells are added/removed/moved?