Can you Countif by cell color?

Account InformationTechRepublic close modalShare with Your FriendsHow to count individual cells within a row by their fill color in Excel Your email has been sent{{#message}}{

Can you Countif by cell color?

Account InformationTechRepublic close modal

Share with Your Friends

How to count individual cells within a row by their fill color in Excel

Your email has been sent

{{#message}}{{{message}}}{{/message}}{{^message}}Your submission failed. The server responded with {{status_text}} (code {{status_code}}). Please contact the developer of this form processor to improve this message. Learn More{{/message}}

{{#message}}{{{message}}}{{/message}}{{^message}}It appears your submission was successful. Even though the server responded OK, it is possible the submission was not processed. Please contact the developer of this form processor to improve this message. Learn More{{/message}}

Submitting

Image of Susan Harkins

by Susan Harkins in Software on July 29, 2020, 5:00 AM PDT

How to count individual cells within a row by their fill color in Excel

Counting records or values in Microsoft Excel is easy. Counting individual cells by their fill color definitely isn't. Using an old function and a matrix tames the problem.We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. For more info, visit our Terms of Use page.

Must-read Windows coverage

  • Windows 11 22H2 is here
  • Checklist: Securing Windows 10 systems
  • Defend your network with Microsoft outside-in security services
  • How to clear the DNS cache on Windows 10

The article How to easily sum values by a cells background color in Excel shows you an easy way to combine built-in features to count or sum values based on the fill colors. This technique requires no special knowledge, but its limited. You cant use it to evaluate individual cells within a row; filtering is a columns-only feature. Furthermore, Excel offers no feature or function to directly evaluate cells by their fill color. In this article, Ill show you how to do so. Its not a clear-cut solution, and without a bit of inside knowledge, youd most likely not figure it out on your own. The thing to remember is that were counting cells, not records.

SEE:Office 365: A guide for tech and business leaders (free PDF)(TechRepublic)

Im using Microsoft 365 on a Windows 10 64-bit system, but you can use older versions. You can work with your own data or download the demonstration .xlsx file. (Im including the menu version format, but it might not work reliably.) This solution isnt appropriate for the browser edition. In addition, this technique works with fill colors applied directly or via styles; it wont work with conditional formatting; that magic requires a Stephen Hawking level Hail-Mary pass.

This article is based on a question sent by Vic Micallef.

The data and the problem

If youre a database person, you think in fields, or columns. Rows are important when youre normalizing tables in a relational database, and then when reporting, but you analyze columns (mostly). Sheets are a bit different, and sometimes you need to evaluate values across columns in a single row. For the most part, Excel handles this situation fine. Unfortunately, theres no built-in function or feature (to my knowledge) that makes it easy to evaluate cells by their fill color, across a row. Before you say it, no, reconstructing your data isnt always a choice.

Now, lets look at a simple sheet, Figure A, where random cells have a light blue fill color. For our purposes, it doesnt matter what the color represents, but it is important to note that the color was applied directly (not conditionally). To count the number in a column, we could use filtering and the SUBTOTAL() function. Because we want to count filled cells across each row, well have to work a bit harder (but at least it isnt rocket science).

Figure A

If you search on this subject, you will find solutions that use GET.CELL to do the same thing that filtering does, but be careful. Those solutions dont count individual cells within a row (or record). For instance, if youre counting the fill color blue in our demonstration sheet, the count for row 3 is 1. Were going to use GET.CELL to create a matrix. Then, well use COUNTIF() to return the number of a specific fill color for each row.

This next step is very important; dont skip it. If youre using the .xlsx format, save the workbook as a macro-enabled file.

How to implement GET.CELL

The CET.CELL is an old function that comes with limitations. Most importantly, you cant directly reference it; in other words, you cant enter =GET.CELL(38,C3) and return the color code for cell C3. Instead, you apply the function to a named range. Second, the way Im using it allows only for counting adjacent cells within a row. If you insert a column between the data set and the matrix youll create later, the function still works, but its one column off. In addition, you cant use it to count (individual) filled cells in a column. Its a very specific solution.

Now, lets put GET.CELL into play. Do the following to create a named ranged that can be used to return any cells fill color:

  1. Click the Formulas tab, then click Define Name in the Defined Names group, and choose Define Name from the dropdown list.
  2. In the resulting dialog, enter a name for the range, such as ColorCode.
  3. In the Refers To control, enter the following expression: =GET.CELL(38,Count!C3), where Count is the sheet name and C3 is the data sets anchor cell. (See Figure B).
  4. Click OK.

Figure B

At this point, nothing has changed visibly. Its time to use the range ColorCode to return codes for filled cells.

How to use ColorCode

Right now, you dont know the color code for any cell in our data set. To remedy that, well create a small matrix, directly adjacent to the data set. (If you leave a blank column between the data set and the matrix, this solution will return erroneous results.)

To create the matrix, enter the following expression in G3:

=ColorCode

which returns the value 37thats the fill color code for C3. Copy that expression to H3:J3. As you can see in Figure C, ColorCode returns the fill color for cells C3, D3, E3, and F3. This is possible because the reference you used when creating the range ColorCode, C3, is relative. If you made that reference absolute, $C$3, it would always return 37. Select G3:J3 and copy the four expressions to the remaining rows, completing the matrix shown in Figure D.

Figure C

Figure D

The matrix isnt dynamic; see the Limitations section below. We still dont know the count of the blue filled cells in any row. Well tackle that next.

How to count the color codes

As is, the matrix returns the fill color codes for each cell in the data set. Using COUNTIF(), we can easily count the blue cells in each row. To accomplish this, enter =COUNTIF(G3:J3,37) in K3 and copy to K14. The values in column K, shown in Figure E, are a count of the number of blue filled cells in the corresponding row. Specifically, 37 identifies the blue filled cells and the function counts only those cells within the corresponding row. Theres only one cell in row 3 where the fill color is blue, 37. In row 2, there are two cells, and so on.

Figure E

Youll probably want to hide or otherwise obscure the matrix in G3:J3. I usually recommend that you not hide cells because theyre easy to forget, making the sheet harder to maintain. If you agree, reduce the width to the barest amount as shown in Figure F and apply back fill color.

Figure F

Variations

You can use GET.CELL to return a lot of information. The value we used, 38, returns a cells fill color. For instance, to sum the values, youd use 5 instead of 38 when creating the range. Then, youd use SUMIF() instead of COUNTIF(). A search on GET.CELL will turn up a list (Microsoft no longer maintains one that I could find.) You can also change the fill code being counted in the COUNTIF() function.

SEE:How to add a drop down list to an Excel cell(TechRepublic)

Limitations

As I mentioned, there are limitationsseveral. The matrix must be adjacent to the data set to work correctly. It wont work for counting filled cells in columns. Because GET.CELL is no longer supported, it could stop working at any time. Modifications arent dynamic. If you change a fill color or update ColorCode, the matrix will notupdate automatically. In both cases, you must re-enter ColorCode, which is a nuisance. For that reason, Im not convinced this is the most efficient solution; if you have a better idea, please share your thoughts in the comments section below.

Stay tuned

Because this solution is such a labor-hog, Ill show you a macro in a follow-up article.Susan HarkinsPublished: July 29, 2020, 5:00 AM PDT Modified: November 2, 2022, 1:15 PM PDT  See more Software

Also See

  • What's in Windows 10 19H2 for enterprises? (TechRepublic)
  • Windows 10: A cheat sheet (TechRepublic)
  • Top Windows 10 run commands (free PDF) (TechRepublic)
  • Power checklist: Securing Windows Server 2016 (TechRepublic Premium)
  • Microsoft delivers first Windows 10 Fast Ring build from its new development branch (ZDNet)
  • 6 simple security changes all Windows 10 users need to make (CNET)
  • Get more must-read Microsoft tips and news (TechRepublic on Flipboard)

Video liên quan