Forum Discussion
use sumif function when criteria is cell hihlighted with a color highlight
I want to use sumif function to add a column of numbers when criteria is a fill color in the cells. How do I do this.
- If you have a criteria in colouring the cells, use it as a criteria in your SumIfs() fn, else you'll have to create a user defined function with VBA to achieve that.
- Thanks for your reply and input. I do not have a Criteria for Cell Coloring (don't know how to do that), and I haveE no idea what VBA is or how to use t. I have used Excel for over 35 years (in Engineering, Finance, & Ocean Navigation), but I am not a Guru in it. Thanks for any additional input.
**bleep**Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the module:
Function CountByColor(ARange As Range, ColorCell As Range) As Long Dim ACell As Range Application.Volatile For Each ACell In ARange If ACell.Interior.Color = ColorCell.Interior.Color Then CountByColor = CountByColor + 1 End If Next ACell End FunctionSwitch back to Excel.
Use the function like this:
In the formula =CountByColor($A$1:$A$19,D1), the first argument $A$1:$A$19 is a range with colored cells, and the second argument D1 is a cell with the color that you want to count.
Save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.
Warning: a formula like this will not automatically update its result when you change the fill color of a cell. It will be updated the next time Excel recalculates formulas; you can force this by pressing F9.
I have attached a sample workbook with the code.
HansVogelaar How do use sumifs with cell color as part of the criteria?
How do you make this auto update when color is added to the cells within the range?
As I mentioned in another reply, updating is not automatic - changing a cell's color does not trigger an event in Excel. You'll have to press F9 to update the result.
Ok, thank you. I missed that post.
