Formula cell color

Formula cell color DEFAULT

Excel formula to get cell color [duplicate]

Anticipating that I already had the answer, which is that there is no built-in worksheet function that returns the background color of a cell, I decided to review this article, in case I was wrong. I was amused to notice a citation to the very same MVP article that I used in the course of my ongoing research into colors in Microsoft Excel.

While I agree that, in the purest sense, color is not data, it is meta-data, and it has uses as such. To that end, I shall attempt to develop a function that returns the color of a cell. If I succeed, I plan to put it into an add-in, so that I can use it in any workbook, where it will join a growing legion of other functions that I think Microsoft left out of the product.

Regardless, IMO, the ColorIndex property is virtually useless, since there is essentially no connection between color indexes and the colors that can be selected in the standard foreground and background color pickers. See Color Combinations: Working with Colors in Microsoft Office and the associated binary workbook, Color_Combinations Workbook.

answered Sep 18 '15 at 20:35

David A. GrayDavid A. Gray

9061010 silver badges1818 bronze badges

Sours: https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color/24384324

Learn How to Fill a Cell with Color Based on a Condition

There are several ways to color format cells in Excel, but not all of them accomplish the same thing. If you want to fill a cell with color based on a condition, you will need to use the Conditional Formatting feature.

Fill a cell with color based on a condition

Before learning to conditionally format cells with color, here is how you can add color to any cell in Excel. 

Cell static format for colors

You can change the color of cells by going into the formatting of the cell and then go into the Fill section and then select the intended color to fill the cell.

In the above example, the color of cell E3 has been changed from No Fill to Blue color, and notice that the value in cell E3 is 6 and if we change the value in this cell from 6 to any other value the cell color will not change and it will always remain blue. What does this mean?

This means that cell color is independent of cell value, so no matter what value will be in E3 the cell color will always be blue. We can refer to this as static formatting of the cell E3.

Conditional formatting cell color based on cell value

Now, what if we want to change the cell color based on cell value? Suppose we want the color of cell E3 to change with the change of the value in it. Say, we want to color code the cell E3 as follows:

  • 0-10 : We want the cell color to be Blue
  • 11-20 : We want the cell color to be Red
  • 21-30 : We want the cell color to be Yellow
  • Any other value or Blank : No color or No Fill.

We can achieve this with the help of Conditional Formatting. On the Home tab, in the Style subgroup, click on Conditional FormattingNew Rule.

Note: Make sure the cell on which you want to apply conditional formatting is selected

Then select “Format only cells that contain,” then in the first drop down select “Cell Value” and in the second drop-down select “between” :

Then, on the first box, enter 0 and in the second box, enter 10, then click on the Format button and go to Fill Tab, select the blue color, click Ok and again click Ok. Now enter a value between 0 and 10 in cell E3 and you will see that cell color changes to blue and if there is any other value or no value then cell color revert to transparent.

Repeat the same process for 11-20 and 21-30 and you’ll see that number changes as per the value of the cell.

Conditional formatting with text

Similarly, we can do the same process for text values as well instead of numerical values by using the “Specific Text” in the first drop down and in the second drop-down select either of 4 values containing, not containing, beginning with, ending with and then enter the specific text in the text box.

For Example:

First, select the cell on which you want to apply conditional format, here we need to select cell B1. On the home tab, in the Styles subgroup, click on Conditional Formatting→New Rule.

Now select Format only cells that contain the option, then in the first drop down select “Specific Text” and in the second drop-down select either of the 4 options: containing, not containing, beginning with, ending with. In the example below, we use beginning with “J” and then select Format button to select Blue as the fill color.

Conditional format based on another cell value

In the example above, we are changing the cell color based on that cell value only, we can also change the cell color based on other cells value as well. Suppose we want to change the color of cell E3 based on the value in D3, to do that we have to use a formula in conditional formatting.

Now suppose if we want to change cell E3 color to blue if the D3 value is greater than 3 and to green if the D3 value is greater than 5 and to red, if D3’s value is greater than 10, we can do that with the conditional format using a formula.

Again follow the same procedure.

First, select the cell on which you want to apply conditional format, here we need to select cell E3. On the home tab, in the Styles subgroup, click on Conditional Formatting→New Rule.

Now select Use a formula to determine which cells to format option, and in the box type the formula: D3>5; then select Format button to select green as the fill color.

Keep in mind that we are changing the format of cell E3 based on cell D3 value, note that the cursor now is pointing at E3, which is the cell we use to set conditional format. The formula “=D3>5” means if D3 is greater than 5 then the value of E3 will change to green. Click ok and see the color of cell E3 changes to green as D3 right now contains 6.

Now let’s apply the conditional formatting to E3 if D3 is greater than 3. This means if D3>3 then cell color should become “Blue” and if D3>5 then cell color should remain green as we did it in the previous step.

Now, if you follow the above steps as we did for Green color, you will see that even if the cell value is 6, it is showing blue color and not green, because it takes the latest conditional formatting we set for that cell, and as 6 is also greater than 3 hence it is showing blue color but it should show green color.

So, we have to arrange the rules we have applied for any particular cells, we can do that by going into Manage Rules option of conditional formatting.

You can see all the rules applied to that cell and then we can arrange the rules or set their priority by using the arrow buttons. A number greater than 5 will also be greater than 3, hence greater than 5 rule will take higher priority and we can move it upward using the arrow buttons.

Now when you enter 6 in D3, the cell color of E3 will become green and when you enter 4, the cell color will become blue.

If you are tired of reading too many articles without finding your answer or need a real Expert to help you save hours of struggle, click on this link to enter your problem and get connected to a qualified Excel expert in a few seconds. You can share your file and an expert will create a solution for you on the spot during a 1:1 live chat session. Each session last less than 1 hour and the first session is free.

Are you still looking for help with Conditional Formatting? View our comprehensive round-up of Conditional Formatting tutorials here.

Sours: https://www.got-it.ai/solutions/excel-chat/excel-tutorial/conditional-formatting/conditionally-format-a-cell
  1. Sub zero kuai liang
  2. Carburetor cleaner brushes
  3. Zx14r swingarm
  4. Mainstays textured towels

Formula or function for IF statement based on cell color

 

Hi Sergei,

 

Thank you for replying. I hope the below makes a little more sense.

 

Below is the criteria I was given and below I have attached a screen shot of the spreadsheet with the relevant columns that my query relates to.

 

  • Make the ‘Status’ column field automatically show the words Active, Dormant or Inactive based on the following conditions:
    • Active- if there were purchases in the current 12 months (third column)
    • Dormant- if there was purchases in previous 12 month period (Second Column) but nothing in Current 12 months period (third column)
    • Inactive- if there was no purchases in second or third column

Note: I'm thinking the answer is utilising a IFS & ISBLANK combined formula, but so far I haven't been able to get this combination to work to encompass all three criteria..if that makes sense. So far I have only been able to get the following IF formula to work but it is not the total solution: =IF(W5=0,("Inactive"),("Active"))

 

Capture1.JPG

Sours: https://techcommunity.microsoft.com/t5/excel/formula-or-function-for-if-statement-based-on-cell-color/td-p/78267
How to Make a Cell Turn a Color in a Formula in Excel : Using Microsoft Excel

How to Count Colored Cells in Excel – A Step by Step Tutorial + Video

Watch Video – How to Count Colored Cells in Excel

Wouldn’t it be great if there was a function that could count colored cells in Excel?

Sadly, there isn’t any inbuilt function to do this.

BUT..

It can easily be done.

How to Count Colored Cells in Excel

In this tutorial, I will show you three ways to count colored cells in Excel (with and without VBA):

  1. Using Filter and SUBTOTAL function
  2. Using GET.CELL function
  3. Using a Custom Function created using VBA

#1 Count Colored Cells Using Filter and SUBTOTAL

To count colored cells in Excel, you need to use the following two steps:

  • Filter colored cells
  • Use the SUBTOTAL function to count colored cells that are visible (after filtering).

Suppose you have a dataset as shown below:

Count Colored Cells in Excel - Dataset

There are two background colors used in this data set (green and orange).

Here are the steps count colored cells in Excel:

  1. In any cell below the data set, use the following formula: =SUBTOTAL(102,E1:E20)
    Using Subtotal function to Count Colored Cells in Excel
  2. Select the headers.
  3. Go to Data –> Sort and Filter –> Filter. This will apply a filter to all the headers.
  4. Click on any of the filter drop-downs.
  5. Go to ‘Filter by Color’ and select the color. In the above dataset, since there are two colors used for highlighting the cells, the filter shows two colors to filter these cells.Using Filter to Count Colored Cells in Excel

As soon as you filter the cells, you will notice that the value in the SUBTOTAL function changes and returns only the number of cells that are visible after filtering.Count Cells with a background color - Filtered Cell Count Subtotal

How does this work?

The SUBTOTAL function uses 102 as the first argument, which is used to count visible cells (hidden rows are not counted) in the specified range.

If the data if not filtered it returns 19, but if it is filtered, then it only returns the count of the visible cells.

Try it Yourself.. Download the Example File

#2 Count Colored Cells Using GET.CELL Function

GET.CELL is a Macro4 function that has been kept due to compatibility reasons.

It does not work if used as regular functions in the worksheet.

However, it works in Excel named ranges.

See Also:Know more about GET.CELL function.

Here are the three steps to use GET.CELL to count colored cells in Excel:

  • Create a Named Range using GET.CELL function
  • Use the Named Range to get color code in a column
  • Using the Color Number to Count the number of Colored Cells (by color)

Let’s deep dive and see what to do in each of the three mentioned steps.

Creating a Named Range

  • Go to Formulas –> Define Name.Count Colored Cells in Excel - Defined Name
  • In the New Name dialog box, enter:
    • Name: GetColor
    • Scope: Workbook
    • Refers to: =GET.CELL(38,Sheet1!$A2)
      In the above formula, I have used Sheet1!$A2 as the second argument. You need to use the reference of the column where you have the cells with the background color. Count Colored Cells in Excel - GetColor

Getting the Color Code for Each Cell

In the cell adjacent to the data, use the formula =GetColor

This formula would return 0 if there is NO background color in a cell and would return a specific number if there is a background color.

This number is specific to a color, so all the cells with the same background color get the same number.Count Colored Cells in Excel - Color Code

Count Colored Cells using the Color Code

If you follow the above process, you would have a column with numbers corresponding to the background color in it.

To get the count of a specific color:

  • Somewhere below the dataset, give the same background color to a cell that you want to count. Make sure you are doing this in the same column that you used in creating the named range. For example, I used Column A, and hence I will use the cells in column ‘A’ only.
  • In the adjacent cell, use the following formula:

=COUNTIF($F$2:$F$20,GetColor)

This formula will give you the count of all the cells with the specified background color.Count Colored Cells in Excel - Color Count without VBA

How Does It Work?

The COUNTIF function uses the named range (GetColor) as the criteria. The named range in the formula refers to the adjacent cell on the left (in column A) and returns the color code for that cell. Hence, this color code number is the criteria.

The COUNTIF function uses the range ($F$2:$F$18) which holds the color code numbers of all the cells and returns the count based on the criteria number.

Try it Yourself.. Download the Example File

#3 Count Colored Using VBA (by Creating a Custom Function)

In the above two methods, you learned how to count colored cells without using VBA.

But, if you are fine with using VBA, this is the easiest of the three methods.

Using VBA, we would create a custom function, that would work like a COUNTIF function and return the count of cells with the specific background color.

Here is the code:

'Code created by Sumit Bansal from https://trumpexcel.com Function GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

To create this custom function:

  • With your workbook active, press Alt + F11 (or right click on the worksheet tab and select View Code). This would open the VB Editor.
  • In the left pane, under the workbook in which you are working, right-click on any of the worksheets and select Insert –> Module. This would insert a new module. Copy and paste the code in the module code window.How to Count Colored Cells in Excel - Insert Module
  • Double click on the module name (by default the name of the module in Module1) and paste the code in the code window.Custom function VBA code to Count Colored Cells in Excel
  • Close the VB Editor.
  • That’s it! You now have a custom function in the worksheet called GetColorCount.

To use this function, simply use it as any regular excel function.

Syntax:=GetColorCount(CountRange, CountColor)

  • CountRange: the range in which you want to count the cells with the specified background color.
  • CountColor: the color for which you want to count the cells.

To use this formula, use the same background color (that you want to count) in a cell and use the formula. CountColor argument would be the same cell where you are entering the formula (as shown below):

Count Colored Cells in Excel - Custom Formula

Note: Since there is a code in the workbook, save it with a .xls or .xlsm extension.

Try it Yourself.. Download the Example File

Do you know any other way to count colored cells in Excel?

If yes, do share it with me by leaving a comment.

You May Also Like the Following Excel Tutorials:

Sours: https://trumpexcel.com/count-colored-cells-in-excel/

Cell color formula

Colors in an IF Function

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10780) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Colors in an IF Function.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

Converting Text to Numbers

Import information from a program external to Excel, and your numbers may be treated as text because of the way that the ...

Discover More

Jumping to a Specific Page

Want to jump to a particular page in your document? Word makes it easy; just pull up the Go To tab of the Find and ...

Discover More

Dissecting a String

VBA is a versatile programming language. It is especially good at working with string data. Here are the different VBA ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

EOMONTH Function is Flakey

Some users have reported problems using the EOMONTH function in later versions of Excel, beginning with Excel 2007. The ...

Discover More

Using a Week Number as One Criterion in a Formula

The SUMIFS function can be quite powerful in conditionally summing information based on criteria you specify. This tip ...

Discover More

Specifying a Language for the TEXT Function

You may want to use Excel to display dates using a different language than your normal one. There are a couple of ways ...

Discover More
Sours: https://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html
IF Statement in Excel Based on Cell Colour

How to count and sum cells based on background color in Excel?

Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color. In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.


Count and Sum colored cells by Filter and SUBTOTAL

Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column. In this situation, we can filtered the Amount column by color, and then count or sum filtered colored cells by the SUBTOTAL function easily in Excel.

1. Select blank cells to enter the SUBTOTAL function.

  1. To count all cells with the same background color, please enter the formula =SUBTOTAL(102, E2:E20);
  2. To sum all cells with the same background color, please enter the formula =SUBTOTAL(109, E2:E20);


Note: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need.

2. Select the header of the table, and click Data > Filter. See screenshot:

3. Click the Filter icon  in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. See screenshot:

After filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. See screenshot:

Note: This method requires the colored cells you will count or sum are in the same column.

One click to count, sum, and average colored cells in Excel

With the excellent Count by Color feature of Kutools for Excel, you can quickly count, sum, and average cells by specified fill color or font color with only one click in Excel. Besides, this feature will also find out the max and min values of cells by the fill color or font color. Full Feature Free Trial 30-day!
ad count by color 2

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now

Count or Sum colored cells by GET.CELL function

In this method, we will create a named range with the GET.CELL function, get the color code of cells, and then count or sum by the color code easily in Excel. Please do as follows:

1. Click Formulas > Define Name. See screenshot:

2. In the New Name dialog, please do as below screenshot shown:
(1) Type a name in the Name box;
(2) Enter the formula =GET.CELL(38,Sheet4!$E2) in the Refers to box (note: in the formula, 38 means return the cell code, and Sheet4!$E2 is the first cell in the Amount column except the column header which you need to change based on your table data.)
(3) Click the OK button.

3. Now add a new Color column right to the original table. Next type the formula =NumColor , and the drag the AutoFill handle to apply the formula to other cells in the Color column. See screenshot:
Note: In the formula, NumColor is the named range we specified in the first 2 steps. You need to change it to the specified name you set.

Now the color code of each cell in the Amount column returns in the Color Column. See screenshot:

4. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown:
A. To count cells by color, please enter the formula =COUNTIF($F$2:$F$20,NumColor);
B. To sum cells by color, please enter the formula =SUMIF($F$2:$F$20,NumColor,$E$2:$E$20).

Note: In both formulas, $F$2:$F$20 is the Color column, NumColor is the specified named range, $E$2:$E$20 is the Amount Column, and you can change them as you need.

Now you will see the cells in the Amount column are counted and sum by their fill colors.


Count and sum cells based on specific fill color with User Defined Function

Supposing the colored cells scatter in a range as below screenshot shown, both above methods cannot count or sum the colored cells. Here, this method will introduce a VBA to solve the problem.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA: Count and sum cells based on background color:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function

3. Then save the code, and apply the following formula:
A. Count the colored cells: =colorfunction(A,B:C,FALSE)
B. Sum the colored cells: =colorfunction(A,B:C,TRUE)

Note: In above formulas, A is the cell with the particular background color you want to calculate the count and sum, and B:C is the cell range where you want to calculate the count and sum.

4. Take the following screenshot for example, enter the formula=colorfunction(A1,A1:D11,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D11,TRUE) to sum the yellow cells. See screenshot:

5. If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:


Count and Sum cells based on specific fill color with Kutools Functions

Kutools for Excel also supports some useful functions to help Excel users to make special calculations, says count by cell background color, sum by font color, etc.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Free Trial Now!

1. Select the blank cell you place the counting results, and click Kutools > Kutools Functions > Statistical & Math > COUNTBYCELLCOLOR. See screenshot:

2. In the Function Arguments dialog, please specify the range you will count colored cells within in the Reference box, choose the cell that is filled by the specified background color in the Color_index_nr box, and click the OK button. See screenshot:

Notes:
(1) You can also type the specified Kutools Function =COUNTBYCELLCOLOR($A$1:$E$20,G2)  in the blank cell or formula bar directly to get the counting results;
(2) Click Kutools > Kutools Functions > Statistical & Math > SUMBYCELLCOLOR or type =SUMBYCELLCOLOR($A$1:$E$20,G2) in the blank cell directly to sum cells based on the specified background color.
Apply the COUNTBYCELLCOLOR and SUMBYCELLCOLOR functions for each background color separately, and you will get the results as below screenshot shown:

Kutools Functions contain a number of built-in functions to help Excel users calculate easily, including Count / Sum / Average Visible cells, Count / Sum by cell color, Count / Sum by font color, Count characters, Count by font bold, etc. Have a Free Trial!


Count and Sum cells based on specific fill color with Kutools for Excel

With the above User Defined Function, you need to enter the formula one by one, if there are lots of different colors, this method will be tedious and time-consuming. But if you have Kutools for Excel’s Count by Color utility, you can quickly generate a report of the colored cells. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Free Trial Now!

1. Select the range that you want to use, and click Kutools Plus > Count by Color, see screenshot:

2. And in the Count by Color dialog box, please do as below screenshot shown:
(1) Select Standard formatting from the Color method drop down list;
(2) Select Background from the Count type drop down list.
(3) Click the Generate report button.

Note: To count and sum colored cells by specific conditional formatting color, please select Conditional formatting from the Color method drop down list in above dialog, or select Standard and Conditional formatting from the drop down list to count all cells filled by the specified color.

Now you will get a new workbook with the statistics. See screenshot:

The Count by Color feature calculates (Count, Sum, Average, Max, etc.) cells by background color or font color. Have a Free Trial!


Related article:

How to count / sum cells based on the font colors in Excel?


Demo: Count and sum cells based on background, conditional formatting color:


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Read More...Free Download...Purchase... 


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Sours: https://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html

You will also be interested:

Sum Cells based on Background Color

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.

Sum based on cell color

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.

Table with colored cells

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.

Adding One More Column To Table with colors

Now, we try to use SUMIF Formula for finding the SUM of cells with yellow background as:

=SUMIF(B2:B13,Yellow,A2:A13)

Sum of yellow cells

Similarly, for finding the SUM of Orange and Green background cells we will use the formulas

=SUMIF(B2:B13,Orange,A2:A13)
and
=SUMIF(B2:B13,Green,A2:A13)

respectively.

Sum_of_colored_cells

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.

FunctionColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
EndFunction

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.

Color index UDF

  1. Now, add one column next to the range that you wish to sum up. In this new column enter the formula as:
=ColorIndex(<refrence_ofcell_whose_background_colour_index_you_wish_know>)

Using Color index UDF

  1. After that, drag this formula to the whole range.

Finding Sum of Colored Cells

  1. 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:

FunctionSumByColor(CellColor As Range, rRange As Range)
Dim cSum AsLong
Dim ColIndex AsInteger
ColIndex = CellColor.Interior.ColorIndex
ForEach cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
EndIf
Next cl
SumByColor = cSum
EndFunction

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.

SUMBYCOLOR_UDF

  1. Now, simply type the “SumByColor” function to call it and pass the following arguments:
=SumByColor(<cell_with_background_color_that_you_wish_sum>, <Range_to_be_summed_up>)

SUMBYCOLOR_UDF

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 get the sample spreadsheet [link] and do let me know in case you face any issues while using these methods.

Sours: https://www.exceltrick.com/how_to/sum-cells-based-on-background-color/


438 439 440 441 442