Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Counting Coloured Cells

I have a spreadsheet that contains various different coloured cells,
I need to be able to:
1. count the number of cells in a given range that are coloured a certain colour.

2. show the answer of a sum involving the above, i.e in range D1:D:5 the sum of 10 + blue cells, minus yellow cells = 12


ABCD12345Sum of 10 (plus blue cells minus yellow cells in range D:1 to D:5) should equal 12

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Formula For Counting Coloured Cells.
I have a table of numbers with conditional formating, formula's are...

=COUNTIF(OFFSET($W3:$AB3,1,0),W3+1)+COUNTIF(OFFSET($W3:$AB3,1,0),W3-1) - color, lemon.
=COUNTIF(OFFSET($W3:$AB3,-1,0),W3+1)+COUNTIF(OFFSET($W3:$AB3,-1,0),W3-1) - color, light blue.

Column AC, I want to count the number of cells that are lemon for each row.
Column AD, I want to count the number of cells that are light blue for each row.

Is it possible?

View Replies!   View Related
Conditional Formatting (the Cell In 'Work' Is Coloured Red, If Not Then It Is Coloured Green)
I have a workbook (see attached) that has 2 sheets, Work and Holiday. I want to be able to enter a persons initials into the wrksheet 'Work' for each day of the week.

If that person is listed on the holiday sheet for that day as 'A' then the cell in 'Work' is coloured Red, if not then it is coloured Green. I have tried to get this to work using a defined list and various IF statements but all to no avail.

View Replies!   View Related
Sum Column With Only Coloured Cells
I have a worksheet containing a large column with random coloured (red) cells, how can I Sum only the coloured cells and then only the empty cells?

View Replies!   View Related
Copying Coloured Cells
managed to count cells based on colour, however if the colour is variable due to conditional formatting then the UDF doesnt work. So my next thought is just to copy the colours into another column next to it and then get the UDF to count those colours. however copying just the colours is not as easy as seems. when i use format painter the colour all comes out the same (once again i am presuming this is due to conditional formatting). the macros etc seem to complicated for this is there a simple thing i am missing?

View Replies!   View Related
Toggle Cells With Coloured Backgrounds
Having searched and read lots of posts, but without finding the answer.

Is it possible to creat a macro that toggles a cell between [empty], [x], [o] and [n/a], with the [x] cell having a background colour of green, the [o] cell having a background colour of red and the [n/a] with a background of yellow.

The above cells won't have anyother function as they will be used as indicators in a progress chart.

View Replies!   View Related
Applying Filter To 'Coloured' Cells
Is it possible to apply Filter utility on Column Cells that are 'coloured'?

View Replies!   View Related
Macro To Count Rows With Coloured Cells
I have a sheet with colour coded cells and I need a macro that counts the number of rows with coloured cells.

I've tried the following code but I always get a count of 0 which isn't right.

Sub Count_Coloured_Cells()

Dim i, c
Dim LastRow As Long
i = 1
c = 0
LastRow = Range("A65536").End(xlUp).Row

Do While i < LastRow + 1
If Rows(1 + i & ":" & 1 + i).Interior.ColorIndex xlNone Then
c = c + 1
End If
i = i + 1

MsgBox (c)

End Sub

Remember, it's the number of rows I need to count (i.e. one row may have several colour coded cells but I only need to count it once).

View Replies!   View Related
Select Specific Coloured Cells From A Range Of Data In One Go
How do you select specific coloured cells from a range of data in one go, without having to scroll through the worksheet and pick them out individually?

View Replies!   View Related
Sum Rows Without Coloured Filled
a macro to sum up rows without coloured.That means the total qty from B11 to B17. For your information, the hightlighted rows vary from time to time. Any guide and help is greatly appreciated.Pls refer to my attachment.

View Replies!   View Related
Displaying Certain Coloured Cell
I have a spreadsheet and certain cells are coloured to represent certain
criterier, I would like to display all of one coloured cells to show status
of that colour, say I want all the red cells locations to be displayed so
that i can check the status.

View Replies!   View Related
Group Items And Divide By Coloured Row
I need to group items by date and time and for each group to be separated by a coloured row. So for example september 3rd at 2am may contain rows of info, after that would be a coloured row.

View Replies!   View Related
Adding A Coloured Marker On Chart Y Axis.
I have a simple Excel 2007 column chart which has names listed along the y axis for each column. Associated with each name in a small table is a colour reference. Is it possible to add a small marker (say a triangle or something) that aligns with each column?

Or possibly (or in addition) colour each column differently and in accordance with the assigned colours? I'm doing this with some vba code. but if someone can point out the general principle I should be able to work that in.

View Replies!   View Related
Reversing Sign Based On Cell Being Manually Coloured
I have a range: AC2:AG1400 that are filled with numbers....

Someone manually highlighted, in bright yellow, some of the numbers.

I need a macro that could reverse the signs of only those that are coloured in that yellow.

View Replies!   View Related
Counting Particular Cells
I am now attempting to do the same type tally except I want a tally for "in conference" Wins/Losses.

I tried the same "SUMPRODUCT" formula as before except this time I held the control key down so as to use only the cells desired. I'm getting "VALUE" for my answer though.

The row I have added in for in conference tally is row 3.

All blue colored cells are for the ACC conference and the green are for the SEC.

Is there a way to get the win/loss figures for selected cells / rows only?

View Replies!   View Related
Counting Cells
I am trying to create a formula to count the # of names up to a certain word. So Cells A1-F1. Are a list of names, the Last name in the list starts with VACANT. How do I create a formula to count the # of names in the list upto the work VACANT? So if VACANT moves to C1 it would decrease the # of names.

View Replies!   View Related
Counting Cells That Are Different
Is there a function in Excel that will count cells that are different from one another? I.e. it would not count the same value twice. For example, in column A:


The result would be 3. However, if I replaced the first "a" with a "d" I would get 4 as a result. Basically I want to know how many different arguments there are in a list. I could not find a function that does this in the Excel list of functions.

View Replies!   View Related
Counting Cells Within Vba
I have written some VBA code to to test if there are 8 names within a range (EF5:FH5, these are 8 merged cells, (4x4 cells)), if there is 8 names within the cells, ie not any empty cells, then the coding will add the new staff name to "trigger_box_1b", and if not then "trigger_box_1a". These trigger boxes are then used to populate further cells.

Private Sub Trigger_Box_1_Change()
Windows("Admin Skills Matrix.xls").Activate 'puts focus on correct spreadsheet
Sheets("Skills Matrix").Select 'puts focus on correct page
Select Case WorksheetFunction. CountIf(Range("EF5:FH5"), 0).double
Case 0
Trigger_Box_1b.Text = New_Staff_Name
Case Else
Trigger_Box_1a.Text = New_Staff_Name
End Select
End Sub

the problems I have be having is that VBA is not recognising the Countif function ("Compile Error: Invalid qualifier", then highlights the Countif). I have tried using a CountA function with the same responce. I have tested the rest of the coding and know that it's working fine (don't think anyone would like to see 75+ pages of VBA coding)

View Replies!   View Related
Counting Cells With Zero But Not Blank Cells
I know this question may have been posted before, but I can not seem to get this to work with my formula. I have a formula to count the values of a column so that I can convert it to a graph. I am giving a value of 1 to cells that contain numbers between 0 and 9.5 and giving a value of 0 to cells that contain numbers greater than 9.5. It works fine except that I have some blank cells that I do not want to get rid of and it is counting those as zeros and giving them a value of one. I want to ignore the blank cells or give them a value of zero.


View Replies!   View Related
Macro To Generate A Line Graph With Coloured Pointers And Lables Based On Table
column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated)
column B = project: x, x, x, x, ,x (name of the project and trend line)
column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph)
column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table)
column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)

NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.

Date Project Delivery Date Status Comments
01/02 x 01/05/2009 Green original date
07/02 x 08/05/2009 Amber delay supply
14/02 x 20/05/2009 Red out of money
21/02 x 30/05/2009 Green on track
28/02 x 28/02/2009 Blue delivered-wow

So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.

Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.

View Replies!   View Related
Counting The Cells That Contain A Specific Value
I am doing a simple count and it returns a value of zero.

I am wanting column D that equals "Macro" to return the number of rows that column D has Macro in it? I used = Count and I used = sumproduct and =count returned me a zero value and there are several hundred rows that meet that criteria and =sumproduct returned a zero value as well????? What am I doing wrong? Is it so simple that I'm missing something?


View Replies!   View Related
Counting Blank Cells
I am trying to write a formula for the following can anyone help please?
I have column D with blank cells and some with purchase order numbers in them. I want to count the blank cells in column D but only if the blank is 3days older than the date recorded in column A. Once I have done this I want to count ther same on every work sheet and I have 24 off them and get one answer.
This will tell us all the outstanding purchase order older than 3days

View Replies!   View Related
Counting # Of Cells That Are Overdue
I have a Column (G) of dates that is the sum of Column (B) and 6 months. I have conditionally formatted Column (G) to turn RED when overdue Yellow when close and GREEN if more than a month out. At the end of Column (G) I would like for it to add all the "RED" blocks and total them.

View Replies!   View Related
Counting Text Cells
I have table in excel 2003, where in one column I have different values in cells, such as A, B, C, D,E, F, etc., etc ( as text values as an example) plus some blank cells, how to calculate number of cells with values different from/not equal to B,C without blank cells?

View Replies!   View Related
Counting Cells With Certain Values
I have a sheet with a bunch of data including dates. I'd like to report out the number of cells that are in a certain month (say January). If I create another column and use =MONTH(A12), I can use COUNTIF(A1:A100,1) to perform this task. This somewhat clunky solution isn't very practicle, becuase I have about 30 column's I'd like to analyze by the date in the column. Is there a more elegant solution where I can incorporate the MONTH(A12) into the COUNTIF equation?

View Replies!   View Related
Counting Text WITHIN Cells
I have a long list of cells each one containing a list of attributes similar to the following:


Bear in mind that the above is in 1 cell and in-between each attribute is an - Alt Enter

Now I want to know how many cells a certain attribute is contained in e.g. "MCE+". I can do this on a one by one basis using the "Contains" Filter. However I would like to have the FULL list of my attributes and know how many times each one is mentioned.

View Replies!   View Related
Counting Cells In A Row With Alphanumerics
I would like to count the number of cells that contain a word. My table looks as follows:


In Column 1 (left column) I have 3 cells that contain the alphanumeric value "BDL". This would yield a non-numeric count of "3". Column 2 would yield a count of "0". I hope I'm being clear. Anyone hav an idea?

View Replies!   View Related
Counting Bold Cells
in the Score column I want a 3 for the SnAkEs team and 7 for the dj2 team. How do I total up the bold cells?

View Replies!   View Related
Counting Only Unique Cells ...
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a

View Replies!   View Related
Simple Counting Cells
way to count cells in a spreadsheet (not values in the cells but just the cells themselves). So I have a whole bunch of cells, some are red, some are blue, some are green based on the info i have to keep track of.

so can i write some COUNTIF for red cells green cells, etc

View Replies!   View Related
Counting Cells That Are Blank
I have attached a spreadsheet with some tele numbers. What i need to do is to find out which column only has 1 telephone number, eg. only a cellphone number, etc. I have aboout 50 000 records that i need to check if only 1 telephone number is available. Can i use the countblank function?

View Replies!   View Related
Counting Cells That Are Not NULL
I have a large number of customers listed in an excel sheet that may recieve a visit from my organisation. The reason for the visit may vary and sometimes a customer may recieve more than one visit. Each Row (or record) maps to a customer. Each column has a visit type which I insert a date in to say when the customer has been visited.

I am looking for a function that will return if a customer has been visited or not. As dates can be summed like numbers I am currently saying in the "Visits Recieved" column =if(sum of dates (visit type colunm) >0, 1,0) Then I simply sum the column to get my answer of how many csutomers have been visited.

View Replies!   View Related
Counting Cells In A Range Up Until A Value
Is there a way I can count the cells in a range up until a certain value and use this # as a variable?

View Replies!   View Related
Counting Empty Cells To The Left
I am trying to count the # of empty cells to the left. I trying to work out how many days since an employee has missed. In this worksheet, a letter is placed in the cell corresponding to the day they missed. And I need to count backwards from a date (say: July 31st) to the last day with a letter in it.

View Replies!   View Related
Value Not Available Error - Counting Blank Cells
I'm trying to to create a formula where if two different cells have no value (K10 & O10) a third cell will also be blank (N10)

I have the following formula which follows these steps when i evaluate it.


=VLOOKUP(" "&O10,$J$77:$K$98,2,0)

=VLOOKUP(" "&" ",$J$77:$K$98,2,0)

=VLOOKUP(" "$J$77:$K$98,2,0)


How can i deliver a blank cell as the answer?

View Replies!   View Related
Counting Cells That Fall Within A Range
I have a spreadsheet in which I have a date column. I would like to be able to count the number of cells that fall within a specified date range and am struggling to find a formula that works.

I've tried - =(COUNTIF(North!N:N, ">23/05/2008")+COUNTIF(North!N:N, "

View Replies!   View Related
How Many Cells Does It Take To Exceed X, And Stop Counting
I am trying to compare a value ( say C80 ) and figure out how many cells it takes to exceed a value ( say -2 ) in a column ( say E79:E2 ) but then actually stopping the count after that first cell count has been triggered.
I'm in a bind - any help would be highly appreicated.

View Replies!   View Related
Counting Cells By Font Colour
I need to count cells in a column that display a red font as a result of conditional formatting triggered by the MAX function in adjacent rows.

I've tried numerous VBA codes suggested by your site and others but without success.

They work if I manually change the font colour but not when they're changed conditionally.

View Replies!   View Related
Counting Top 3 Highest Valued Cells
I need a SIMILAR formula to this one posted 2/21/02 by Juan Pablo Gonzalez-

***I need to count the 3 highest valued cells out of 20, except they are listed on a different workbook.

View Replies!   View Related
Counting Blocks Of Highlighted Cells
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up... I can SEND THE SHEET IF YOU WISH....

J F M Ap M....
acct 1

acct 2

acct 3

View Replies!   View Related
Counting Blank Cells Using Sumproducts
I am trying to put together a table from which I'll attach a chart showing-at a glance-where open complaints are in our process:

are they open, closed, waiting for samples, waiting for QC, QC'd pending admin approval, etc.

my log consists of a lot of columns which track various pertinant info about each complaint. Such as A-complaint ID, B-date opened, C-date closed, K-initial QC investigation, L-final QC, M-admin status (pending/approved/denighed), N-notes (boiler plate notes like "pending ra samples" or "closed").

I used this formula alot in my various analyses =SUMPRODUCT(--(YEAR(Log!open)=$O74),--('ComplaintLog (101408).xls'!iqc="pending")) without issue.

Until now. I want to search multiple criteria starting with the year (2009, in this case) and whether or not the issue is closed (date in column C). Only need a count.

How do I append the sumproduct formula? I've looked at isblank but get crazy results from #value to #name to 340 (I only have 292).

View Replies!   View Related
Counting Cells With Multiple Conditions
I have a large list of items and I want to be able to count and sum up the cells based on multiple conditions. Lets just say for example I have a list like this, also the list updates and adds more rows for more information, so its not confined.

xxx Open
xxx Complete
yyy Complete
yyy Open
xxx Complete
xxx Open

I want to be able to count how many xxx are Open. I assume it involves either CountA, CountIF, If, And but I keep getting errors when It searches for text. I can use CountA and have it search for text of a single columb just fine, but I cant do contitions based on multiple columbs with just a cell formula.

View Replies!   View Related
Countif: Counting With Commas In Cells
how do to count the number of occurrences of a text string in a range of cells, where some cell have comma delimited entries?

I am trying to count the number of times a project number is identified in a column of cells. However, in any row in that column a cell may have multiple project numbers referenced, separated by commas.

Using countif Excel thinks that the cell has a different entry and it won’t include it in the count even though the criteria string is in the cell.

View Replies!   View Related
Counting Colored Cells Not Updating
I am currently using the following formula to count the number of colored cells that are yellow. Problem is, I can't get the formula
to automatically update when a new cell is shaded to yellow. I have to click on the cell with the formula, then click in the formula field
and hit the enter button. I do not have "manual calculations" checked in the options tab.


View Replies!   View Related
Counting Cells That Display Data
obviously if one wants to count all cells that contain data they can use COUNTA, but what if i have a range of cells that contain IF formulas and only want to count the cells that display data?

presumably you'd have to use some variation of NOT(""), but i can't seem to make it work.

View Replies!   View Related
Counting Consecutive Cells That Are Greater Than Or Less Than 0
My goal is to look at a column of numbers and count the number of consecutive cells that are positive (or negative), while ignoring blank cells that may be in between them. Using the first formula on this thread.

I was able to adapt it to my own data, but whenever I had blank cells in my data the results were not reliable. the formula i am using now is =IF(COUNT(1/((A1:A27<0)*(A2:A28<0))),MAX(FREQUENCY(IF((A1:A27<0)*(A2:A28<0),ROW(A2:A28)),IF((A1:A27<0)*(A2:A28>= 0),ROW(A1:A27))))+1,0). for a bunch of data in column A. With limited knowledge of excel I'm not exactly sure how this formula works, so I'm not sure how to tell it to ignore all blank cells. I attached an example that shows how it fails with the blank cells

View Replies!   View Related
Counting Cells That Are Less Than Todays Date
I have a column of cells with data like the following


I need a way in VB to say tell me the amount of dates that are less than todays (09-Dec-09) date. Using the above list the answer should be 2. Every formula I try returns the value 0.

View Replies!   View Related
Counting Values In Cells And A Range
I have a column, we'll say E18:E2500. In the cells in that column are four digit numbers. Some of these cells may have multiple four digit numbers separated by a comma and a space. (example: 2020, 2100, 3120) Some other cells in the column may also share the four digit numbers (I mean duplicates).

So I'm trying to write a formula to sum and count all the unique values in the cells and in the range. This is what i've been trying to use but it counts all the values with no regard to duplicate values:

=SUM(IF(LEN(TRIM($E$18:$E$2500))=0,0,LEN(TRIM($E$18:$E$2500))-LEN(SUBSTITUTE($E$18:$E$2500," ",""))+1))

View Replies!   View Related
Counting Different Cells With Variable Data
how to sum 3 cells when 2 out of 3 cells match.

Here is the data.

Cells A1:A10 = Florida
Cells A11:A20 = Florida State
Cells A21:A30 = ~Florida
Cells b1:b5 = W
Cells b6:b25 = L
Cells b26:b30 = W

Sum all cells that have "Florida", "~Florida" and "W" in common.

View Replies!   View Related
Counting Cells With Multiple Criteria
Column A contains "Power","Instr","EHT", Column AJ contains "25M32", "25M35","25M39"and the a date is in column AE. I need to count how many time the date appears in AE if column the cell in A is "Power" and the cell in AJ is "25M32".

View Replies!   View Related
Counting Cells And Ignoring References
I have a range of cells (B6:M6) and I want to count how many have data. My problem is that all of the cells reference cells on another worksheet. Every cell starts out like =Bethlehem!B6. I want to ignore cells that have a reference in it when I count. I know how to do it if you want to ignore these cells as follows: =countif(b6:m6,"<> 0"). Those cell references return a 0 if they have no data in them. However, a user might actually enter a 0 in the referenced cell and I need to count that one. The above formula would exclude it.

View Replies!   View Related
Copyright © 2005-08, All rights reserved