Select the range, click on the Sum by Color icon and choose one of the options: Sum and Count by one Color or Sum and Count by All Colors. If you've chosen to work with only one color, click the Color Picker icon next to the Pick any cell with color field and select a pattern cell in your table that represents the formatting you need.
Macabacus includes several cycles and other tools for coloring cells and chart elements. Colors used by these tools can be customized in the
Excel > Format > Colors section of the Settings dialog. When customizing these colors, you can choose from any color in your Macabacus color palette. If you want to use a color in your cycle that is not available in your palette, you must first modify your palette to include that color.
When cycling through colors in a cycle using a keyboard shortcut, and the last color in the cycle is reached, repeating the keystroke applies the first color in the cycle. While color cycles are intended for use with shortcut keystrokes, you can also access the colors in your color cycles under the
Macabacus > Format > Color menu.
Alternate the font color of selected cells between blue and your default font color (typically black) using the Blue-Black Toggle. Blue and black are the font colors used most often in financial modeling, where blue is used to denote inputs and black is used for formulas that reference only cells within the same worksheet. The blue color used by the Blue-Black Toggle cannot be customized.
Font Color Cycle
The Font Color Cycle consists of several customizable font colors that you most frequently use in your modeling. Key the Font Color Cycle shortcut to apply the next font color in the cycle to all selected cells.
Default Font Color
The default font color is used for optionally recoloring fonts when exporting cells to PowerPoint/Word and generating PDFs. Black is the default font color, which can be customized.
Recolor colors are font colors that Macabacus will recolor to the default font color when exporting cells to PowerPoint/Word and generating PDFs. Normally, these colors will be similar or identical to your AutoColor colors.
No AutoColor Colors
In some cases, you may not want an AutoColor operation (discussed below) to override existing font colors that have special meaning. For example, if you colored a numeric input red as a reminder to update it later, you would not want AutoColor to change the number's font color to blue. To avoid unintended AutoColoring, add red to the list of No AutoColor colors in the Settings dialog. When you later perform an AutoColor operation, cells with red fonts will not be modified. When customizing your No AutoColor colors, note that just one or two colors may be sufficient.
In financial and other types of modeling, font colors are commonly used to visually characterize cell content. For example, numeric inputs are typically colored blue. Macabacus lets you create an AutoColor scheme that defines which font colors to use for cells containing numeric inputs, partial inputs, formulas that reference cells on the same worksheet, formulas that reference cells in other worksheets and workbooks, hyperlinks, and formulas that contain external data functions (e.g., FactSet or CapIQ 'pulls').
The default AutoColor scheme that installs with Macabacus reflects the finance industry standard, but you can modify these colors as desired in the Settings dialog. In practice, your AutoColor colors should be the same as or similar to those in your Font Color Cycle.
What is a partial input?
Partial inputs are formulas that contain one or more inputs, or 'hardcoded' values, such as =A1+12.34. This formula is effectively an input because we have hardcoded the value 12.34 into the formula. Accordingly, you might expect AutoColor to apply the same font color to this cell that it applies to normal input cells (those containing just a number). If so, specify the same color for inputs and partial inputs in your AutoColor scheme. Alternatively, assign a unique color for partial inputs.
Whether a formula is a partial input can be subjective. The formula in the example above is clearly a partial input, but what about the formula =CHOOSE(2,A1,A2,A3)? Does the value '2' constitute a partial input? Macabacus' AutoColor algorithm makes that determination for you, and you may not always agree with it. If this happens often, you can disable AutoColoring for partial inputs by removing the partial inputs color from your AutoColor scheme.
Macabacus ignores the values 0, 1, 100, 1000, and 1000000 in formulas when checking for partial inputs.
Apply the AutoColor scheme to all cells in the selected range.
Apply the AutoColor scheme to the active worksheet.
Apply the AutoColor scheme to the entire active workbook.
AutoColor on Entry
With this feature enabled, Macabacus will automatically color cell fonts according to your AutoColor scheme as you enter cell values and formulas.
AutoColor on Entry may slow down some operations involving large numbers of cells, and may adversely impact Undo/Redo behavior. For this reason, AutoColor on Entry is disabled by default.
The AutoColor Cycle is comprised of the customizable font colors in your AutoColor scheme. Key the AutoColor Cycle shortcut to apply the next font color in the cycle to all selected cells.
Fill Color Cycle
The Fill Color Cycle consists of several customizable cell fill colors that are most frequently used in your modeling. Key the Fill Color Cycle shortcut to apply the next fill color in the cycle to all selected cells.
Alternate Row/Column Shading
Shade odd/even rows/columns in the selection the default shading color. Because Macabacus uses conditional formatting to achieve alternate shading, alternate shading will persist as rows/columns are inserted and removed. Alternate shading can be cycled using a keyboard shortcut, or applied using the buttons on the
Macabacus > Format > Color > Row/Column Shading menu.
Note that conditional formatting is 'volatile,' and may slow down Excel when used extensively or applied to very large cell ranges. You can alternatively apply alternating row/column shading using traditional (i.e., non-conditional) formatting with Macabacus' Modify Rows and Modify Columns tools.
Default Shading Color
The default fill color is used to shade alternate rows/columns. Light gray is the default font color, which can be customized.
Border Color Cycle
The Border Color Cycle consists of several customizable border colors that you most frequently use in your modeling. Key the Border Color Cycle shortcut to apply the next border color in the cycle to all selected cells. Note that Border Color Cycle only changes the color of existing borders and will not add new borders to selected cells.
Default Border Color
The default border color is the border color applied when using border style cycles and the Sum Bar tool. Black is the default border color, which can be customized.
Chart Color Cycle
The Chart Color Cycle consists of several customizable chart colors that you most frequently use in charting. Key the Chart Color Cycle shortcut to apply the next chart color in the cycle to the selected chart element.
Chart properties that can be colored using this tool include series fill color, series line color, data point fill color, chart area fill color, plot area fill color, gridlines color, and legend fill color. We recommend adding only your most frequently used chart colors (including perhaps a gridline color) to the Chart Color Cycle to limit cycle time.
Chart Series Colors
You can recolor series in selected charts to your customized series colors by clicking the
Macabacus > Charts > Recolor Chart > Recolor Series to Defaults button. This is a quick way to apply your preferred color scheme, without having to deal with Office's more cumbersome chart templates. If you specify six chart series colors, and your chart has seven series, for example, the Series 1 color will be applied to Series 7.
This documentation refers to Macabacus version 8.14.5. Some features and descriptions of these features may not
apply to older versions of Macabacus. Update your Macabacus software to take advantage of the latest features.
Few weeks back, one of my regular visitors dropped me an email describing an issue that he was facing. His task was to add the contents of certain cells based on their background colors.
As we all know, Excel by default has no formula or feature to calculate such a thing. So, in this post I will share few methods that will help you to achieve this.
To make the task more clear let’s have a look at the below image.
This image depicts that here we don’t need the total sum of all the elements but instead we want the sum of elements that have the same background color.
Recommended Reading: Weighted SUM in Excel
Method 1: SUM cells on the basis of background colour using SUMIF Formula:
We know that SUMIF function is a combination of SUM and IF formula and hence SUMIF can come quite handy for adding cells based on color.
If you don’t know how to use a SUMIF Function, then before going any further I would strongly suggest you to read this post.
First of all let’s try to understand how we are going to do this:
Consider we have a table as shown in the below image.
Next, we will add one more column to this table where we will manually type the background colors of their adjacent cells as shown in the below image.
Now, we try to use SUMIF Formula for finding the SUM of cells with yellow background as:
Similarly, for finding the SUM of Orange and Green background cells we will use the formulas
But, as we can see that this method is quite cumbersome, particularly if we need to use this on lists with hundreds of elements.
So, what’s the faster alternative?
To make the above process easier to use we need to reduce the effort of writing cell background colors manually.
So, for this task we can use a small user defined function (UDF) which will do the trick for us.
Note: This function does not return the color name but it returns the color index which is also a unique value and can be used in our task.
Follow the below steps to use the UDF:
1. First of all open your worksheet where you need to add the cells based on background colors.
2. Next, press ALT + F11 to open the VB Editor. Navigate to ‘Insert’ > ‘Module’.
3. After this, paste the “ColorIndex” UDF in the Editor.
4. Now, add one column next to the range that you wish to sum up. In this new column enter the formula as:
5. After that, drag this formula to the whole range.
6. Now, you can use the SUMIF function to add the cells that have same background color as shown in the above image.
Method 2 : Using a much faster and better UDF:
The UDF that we are going to use in this method is simply an extension of the above used function.
This Function is as under:
How to use this UDF:
Follow the below steps to use this Function:
1. Open your target worksheet.
2. Press ALT + F11 to open the VBA Editor and navigate to ‘Insert’ > ‘Module’.
3. Paste the “SumByColor” Function in the Editor.
4. Now, simply type the “SumByColor” function to call it and pass the following arguments:
Note:In the formula shown in above image instead of the first argument “A2” we could have also used any one of A2, A5, A8, A10, A12. Because all these cells have yellow background.
In our case we can use the following formulas:
- Sum of Yellow Cells:
=SumByColor(A2,A2:A13)[As ‘A2’ is the address of yellow cell and A2: A13 is the range to be added]
- Sum of Orange Cells:
=SumByColor(A3,A2:A13)[As ‘A3’ is the address of orange cell and A2: A13 is the range to be added]
- Sum of Green Cells:
=SumByColor(A4,A2:A13)[As ‘A2’ is the address of green cell and A2: A13 is the range to be added]
So, this was all from me about this topic. Don’t forget to download the sample spreadsheet [link] and do let me know in case you face any issues while using these methods.