In Excel, the SUMIF function is typically used to sum numbers based on specific conditions that target cell contents. However, many users ask whether it's possible to sum based on a cell's fill color.
Can SUMIF sum by cell color?
In fact, SUMIF cannot directly sum by cell fill color. However, we can use a method to convert the fill color into a number, thereby enabling summation by color.
How to convert cell fill color to a number?
We can use Excel's macro sheet functions (also called 0 functions) in GET.CELL function to convert the fill color into a number. Macro sheet functions were introduced in Excel version 4; although they cannot be used directly in cells in current versions, they can still be called via defined names.
Macro sheet functions — capabilities
- Macro sheet functions, also called 0 functions, are from Excel version 4; for compatibility, current versions can still call them.
- Macro sheet functions can do things that current functions or tricks cannot, such as retrieving a cell's fill color value or getting a list of worksheet names.
- Macro sheet functions cannot be used directly in cells; you must first define a name and then use that name in a cell.
Solution steps
- Define a name：
- In Excel, select 'Name Manager' from the Formulas menu.
- Enter a custom name in the 'Name' field, for example
color。 - Enter the formula in the 'Refers to' field:
=GET.CELL(63, 工作表1!$B2)&T(NOW()). This returns the fill color index of the specified cell.
- Use the name to get the fill color index：
- In cell C2 enter
=color, which will retrieve the fill color index for that cell.
- Summing by fill color：
- In cell D2 enter
=SUMIF(C2:C12, C2, B2:B12), so you can sum based on the fill color index.
- Refresh fill color changes：
- If any cell's fill color changes, press F9 to recalculate.
Conclusion
By following the steps above, even though Excel's SUMIF cannot sum by fill color directly, we can use macro sheet functions to convert fill color to numbers and thus sum by color. This provides more flexibility for data analysis, especially when managing data visually by color.
