OmniDocHow to do summation by color in Excel: Color summation formula settings
The status is marked with fill color or font color in the table. When you want to calculate the amount by color, the ordinary summation formula is invalid. This article talks about three color summation methods: filtering and adding subtotals, color number auxiliary columns, and custom functions, and explains how to set up and check each.
Key points of this article
- Ordinary SUM and SUMIF only recognize numerical values and text, not cell colors. To sum by color, you must first convert the color into computable information.
- When there are few colors and the results are only viewed once, it is faster to use SUBTOTAL(109) after filtering and there is no need to change the formula.
- When repeated statistics are needed, use the name plus GET.CELL to obtain the color number, and then use SUMIF to sum by number. After modifying the color, press F9 to refresh.
- The colors generated by conditional formatting are usually not readable. You should directly use the conditional rules themselves to judge, or change to manual coloring.
- After finishing, you need to manually calculate a set of numbers for comparison to confirm that the result is consistent with the visible color.
Commonly used colors in the form mark the status: red means overdue, yellow means pending review, and green means paid. When doing summaries, we often ask, "What is the total amount of red?" However, SUM and SUMIF only look at the numerical value and text, not the background color or text color of the cell, so directly applying the formula will get wrong results, or you will simply not be able to start.
This article talks about how to use and set up the color summation formula, which is suitable for desktop spreadsheet software such as Excel. After reading, you can judge which method should be used for your own table, set up the formula according to the steps, and finally check whether the result is reliable.
How to sum by color? One sentence conclusion
Color summation does not have a built-in function that can be used by directly writing conditions like SUMIF. The key is to first turn the "color" into a number or status that can be recognized by the table. The table below can help you locate quickly.
| your situation | Recommended practices | reason |
|---|---|---|
| There are only a few colors, you only need to see the results once | Filter by color and sum using SUBTOTAL(109, area) | No need to write complicated formulas, only visible rows will be counted after filtering |
| Statistics need to be repeated, and the color will change frequently. | Define the name and color number, and use the auxiliary column with SUMIF | The formula can be reused, just refresh after changing the color. |
| Often need to do multiple sets of statistics based on color, and can accept macros | Write a custom function to return results by cell color | Write the formula once and the parameters can be flexibly adjusted |
| Colors are automatically generated by conditional formatting | Directly use the judgment column corresponding to the conditional rule to sum | Conditional formatting colors are usually not readable and need to be collected from the source of the rules. |
Before you start: Confirm these points first
1. Are the colors filled in manually or generated by conditional formatting? Manually filled colors can be read; colors displayed in conditional format cannot be obtained by most reading methods, and forced statistics will miss the calculation. This determines which path you should take.
2. Are you counting fill color or font color? The two methods of obtaining values are different. The fill color is the cell background, and the font color is the text color. If the statistics are of the wrong type, the results will be all 0 or misaligned.
3. Whether the data is in the same continuous area. Color summation relies on "color and amount in the same row" correspondence. If there are merged cells or blank rows in the middle, the numbers will be misaligned and should be organized into a standard table first.
4. Is it possible to accept files saved as macro-enabled. Custom functions require macro support, and computers in some units will prohibit macros. If that doesn't work, choose the filter or auxiliary column method.
5. Whether the data source is PDF. If the color table only exists in PDF, it must be converted into a table before processing. OmniDoc's PDF editor supports exporting PDF to Excel, and then statistics according to the method in this article. You can download the desktop client from download.omnidoc.top.
Steps to use auxiliary columns to add color numbers and then sum
This is a commonly used and easily reusable method. The idea is: first get the color number for each cell, and then use SUMIF to summarize by number.
- Confirm the data area. Assuming the amount is in column C, rows 2 to 100 are the data, and the color markers are in column B or the cell where the amount itself is.
- Create a new name. Create a new name in the name definition function of the spreadsheet software, for example, name it ColorIdx, and write the reference position as the GET.CELL function. The first parameter 63 means reading the fill color, 24 means reading the font color, and the second parameter points to the first data cell, for example, a relative reference written as the worksheet name plus A2.
- Write the auxiliary formula in the empty column. Enter the equal sign plus ColorIdx in row 2 of the empty column next to the data, then fill down to the last row. This column shows the color number of each row.
- Refresh calculation. GET.CELL will not automatically update as the color changes. You need to press F9 to refresh before the number will appear or update.
- Determine the number of the target color. Find a cell that has been confirmed as the target color, look at the number it displays in the auxiliary column, and write down this number.
- Write summary formula. Enter SUMIF in the statistical cell, select the auxiliary column in the condition area, write the target number as the condition, and select the amount column in the summation area. For example, the summation result is to add the amount by number.
- Refresh after modifying the color. Every time you change the background color or font color, press F9 to recalculate and see if the summary result changes.
- Multiple colors are counted separately. Repeat steps 5-6 for each color to get a total for each color.
Steps to quickly sum using filter plus SUBTOTAL
If you only want to see the results temporarily and do not want to maintain the name and auxiliary columns, you can filter first and then sum.
- Enable filtering for the header. Select the row with the table header to open the filtering function of the spreadsheet software.
- Filter by color. In the filter menu of the amount column or color column, select Filter by color to keep only rows with the target color.
- Enter the SUBTOTAL formula in a blank cell. For example written as SUBTOTAL(109, C2:C100), where 109 means summing and ignoring invisible rows.
- Confirm the result. After filtering, only the rows of the target color remain, and the formula result is the sum of these visible rows.
- Clear filters and view again. After canceling filtering, the result of SUBTOTAL will be restored to the total of all data, please pay attention to the distinction.
The disadvantage of this method is that it requires manual filtering every time and is not suitable for automatic updating in reports.
Check each item after completion
After the statistical results come out, do not hand them over to others directly. Use the table below to check them item by item first.
| Check items | How to see | What should you do if there's a problem? |
|---|---|---|
| Check if the color numbers are correct | Spot check a few rows and compare the numbers to the actual colors in the auxiliary column | Confirm whether the starting cell referenced by the name corresponds to the first data entry; if offset, correct it |
| Do the results update with the color? | Change the color of a cell and press F9 to see if the total changes | If there is no change, it means the name is not active or the calculation is set to manual. Check and refresh |
| Check if conditional format colors are omitted | Compare the manually filtered quantity with the number of rows counted by the formula | If the conditional format color cannot be read, use conditional rules to check column statistics |
| Check whether the total matches the manual results | Randomly select about ten rows, sum them up with a calculator, and compare with the formula | Differences usually come from duplicate rows, merged cells, or hidden rows, checked line by line |
| Check whether font color and fill color are mixed | Look at the name—is it 63 or 24? | When counting font colors, change the parameter to 24 and refresh again |
It should be noted that color summing depends on the color itself, so if someone changes the color during the counting period, the result will change accordingly. Before the formal report, try to double-check and write the color rules next to the form for others to review the details. Tables recognized by OCR or machine-translated explanatory text also need to be manually checked for important content.
📊 Core Comparison
Each of the three methods has its own applicable scenarios and can be compared from operational costs, whether automatic updates are needed, and whether macros are needed.
| Method | Operating costs | Can the color update automatically after it changes? | Whether macros are needed | Suitable for the scene |
|---|---|---|---|---|
| Filter and add SUBTOTAL | Low, do not change the formula | No, changing colors requires re-filtering | No | A quick look at the view |
| Name plus auxiliary column SUMIF | In this context, it is necessary to understand the names and relative references | Yes, press F9 to refresh | No | Periodic reporting |
| Custom functions | Lower and concise formulas | Yes | Yes, files must be saved as macro-enabled files | Frequently do multi-group statistics |
If you only want to do a one-time check, just select and filter; If you need to keep weekly or monthly statistics, it's recommended to use auxiliary column methods to fix the settings.
❓ Frequently Asked Questions (FAQ)
1. How to write the color sum formula?
Ordinary tables do not have built-in formulas for direct summation by color. A common way to write it is to first add GET to the name. CELL gets the color number, then uses SUMIF to sum the numbers, for example, SUMIF plus auxiliary column area plus target number plus amount area. The specific format should be adjusted according to your data location.
2. How do you use Excel formulas to recognize font colors?
The font color is also GET. CELL, but the first parameter is changed to 24. First, write the name reference as the font color number. After refreshing the auxiliary column, you can see the number corresponding to each line of text color, then use SUMIF to count by number.
3. Why can't the colors in conditional formatting be counted?
The color displayed in conditional formatting is not the fill color of the cell itself, GET. CELL is usually not readable. A safer approach is to write the judgment corresponding to the condition rule into a column of results, for example, if the overdue column shows "Yes" or "No," then apply a SUMIF to that column.
4. If you change the color, why doesn't the total change happen?
Color coding needs to be recalculated. Press F9 to refresh, and the auxiliary column and summary results will update. If the issue still doesn't change, check whether the name reference is correct and whether the table's calculation method is set to manual.
📝 Summary
The key to summing by color is to first convert the colors into computable numbers or states, then summarize them using SUMIF or SUBTOTAL. Temporary statistics use filter plus SUBTOTAL, long-term reports use color codes plus SUMIF. No matter which method is used, after finishing, manually verify the data to confirm that the color rules match the results.
