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:
