-
The forum (and all other resources in Chandoo.org) will be under maintenance and will be unavailable for a maximum of two hours on the first week of October 2023. The exact date, time and duration of the maintenance are subject to change. We regret the inconvience it may cause.
Sumbycolor
- Thread starter Sanussha
- Start date
A quick google search would have shown you this:
www.ablebits.com
Excel: count and sum cells by color
A quick way to sum and count colored cells in Excel by using custom functions and macros: in a range or across entire workbook.
Here are a few options to add or enable the SUMCOLOR function in Excel on Windows 11:
>>> moderator note >>> Do not try to add hidden links to somewhere <<<
- Make sure you have the latest version of Excel. SUMCOLOR was introduced in Excel 2013, so you need Excel 2013 or newer. Update to the newest version if needed.
- The SUMCOLOR function may be disabled by default in some installations. Go to File > Options > Add-ins. In the Manage box, click Go and enable the Analysis ToolPak add-in if it is disabled.
- You may need to manually add the Analysis ToolPak add-in. Go to File > Options > Add-ins > Manage Excel Add-ins > Go. Check if Analysis ToolPak is listed, if not click Browse and add it from the install folder.
- Enable the SUMCOLOR function manually. In the Name Manager (Formulas tab) click New and define the name SumColor with the following formula: =SUMPRODUCT(1*ISNUMBER(SEARCH(","&COLOR_INDEX&",",","&SUBSTITUTE(CELL("color",A1),".","")&",")))
- Use VBA to create the SUMCOLOR function. Add a new module and define a Function SumColor() with the above formula. Then you can use SumColor in worksheets.
- As a workaround, use COUNTIF with cell color criteria. For example: =COUNTIF(A:A, "cell-color-criterion")
>>> moderator note >>> Do not try to add hidden links to somewhere <<<
- Make sure you have the latest version of Excel. SUMCOLOR was introduced in Excel 2013, so you need Excel 2013 or newer. Update to the newest version if needed.
- The SUMCOLOR function may be disabled by default in some installations. Go to File > Options > Add-ins. In the Manage box, click Go and enable the Analysis ToolPak add-in if it is disabled.
- You may need to manually add the Analysis ToolPak add-in. Go to File > Options > Add-ins > Manage Excel Add-ins > Go. Check if Analysis ToolPak is listed, if not click Browse and add it from the install folder.
- Enable the SUMCOLOR function manually. In the Name Manager (Formulas tab) click New and define the name SumColor with the following formula: =SUMPRODUCT(1*ISNUMBER(SEARCH(","&COLOR_INDEX&",",","&SUBSTITUTE(CELL("color",A1),".","")&",")))
- Use VBA to create the SUMCOLOR function. Add a new module and define a Function SumColor() with the above formula. Then you can use SumColor in worksheets.
- As a workaround, use COUNTIF with cell color criteria. For example: =COUNTIF(A:A, "cell-color-criterion")
Last edited by a moderator:
