Forum Discussion
Excel - Add value based on colour
Hi All, I have an excel sheet where I am trying to create a cash flow estimate based on a programme of building works. Column C has the planned week number, Column D has the planned duration...
I know this is a long shot because this thread is 2 years old, but I am giving it a shot.
Hey, this worked nearly great for me. However, it is rounding the total. Is there a way to not do that?
Here is a screenshot of my total lines so you can see what I mean:
There are at least two possible sources of error:
- Data type - The code supplied by Logaraj Sekar​ sums into a Long variable. Long variables can only contain integer values. As your Total numbers include decimal values, maybe you have changed the data type; but I could speculate that your formulas there are calculating using the custom function SumByColor and are adding in some other values not dependent on color.
- Timing - A problem in calculating based on fill color (or text color or possibly other properties) that is set by a conditional formatting (CF) rule is that cell formulas (including the custom functions they use) are calculated first; then CF rule formatting is applied. So a change to a value in a cell will occur before the fill color might be changed.
The latter is one reason I recommend against doing color-based selection (filtering). Instead, create some categorization values in helper cells (which can be hidden from view), and FILTER (or GROUPBY) based on those values.