count the cell that are in colour background (which set by conditional formating)

[magicbean]

Hi, i am new here and i need your help!

I have been assigned a task to create one table by counting how many cells are in red, yellow, blue, white colour.

red: indicate the certificate which will expired within one month

Yellow: indicate certificate which will expired within 2 to 3 month

blue: indicate certificate which expired

white (without any colour): indicate certificate that will expired in the future which more than 3 month

The table shall consist of "non-expired cert" - red and yellow; expired cert and total cert.

It would be great if you can guide me, Thanks

Actly i wish to attach a file regarding this matter but i couldnt found the attachment icon.
​

 

[Akashwani]

Hi and welocme to MrExcel.

First off, I assume this is the same question....
http://www.mrexcel.com/forum/excel-...detecting-conditional-formattings-colour.html

It is best if you don't keep posting the same question over and over, but *bump* your original post after you have waited for a reasonable period of time for a reply.

I don't think there is a need to count the cells with a particular Conditional Formatting based on the formatting colour, this isn't easy to achieve, but you can take a look here if you like......

http://www.xldynamic.com/source/xld.CFConditions.html

You need to go down to this heading.....

A Conditionally Formatted Colour Counting Function

Personally I think another way to do this would be to count the number of cells that meet your original Conditional Formatting rules.
Which formulas have you used to set your Conditional Formatting?
Can you post a sample of your data and ALL the formulas you have used for Conditional Formatting rules?

I hope that helps.

Ak

 

[magicbean]

Thanks for your reply. sorry i cant post my sample data at here, but i do got post at other excel forum. By clicking the link below you will find my attachment.

count the cell that are in colour background (which set by conditional formating)

At "Expiry" column:
For red colour cell: cell value between ($C$3+1) and ($C$3+30)
For Yellow colour cell: cell value between ($C$3+30) and ($C$3+90)
For white colour cell: cell value >$C$3+90
For blue colour cell: cell value <$C$3

 

[Akashwani]

Hi,

I cannot access your file on that site as I'm not a member.

Based on what you have posted for the "Expiry" column, I have put together some sample data and what I think may be the solution you require.

Excel Workbook
	B	C	D	E	F	G	H	I	J
1				Date1	Difference		CF Formula Rule	Count
2				02/01/2013	1		C3+1 & C3+30	2
3		01/01/2013		20/02/2013	50		C3+30 & C3+90	2
4				11/04/2013	100		>C3+90	3
5				22/12/2012	-10		1
6				31/01/2013	30
7				02/03/2013	60
8				31/05/2013	150
9				20/07/2013	200
10
Sheet4

From this you can see that I have made up the dates and I have added a column (Difference) just as a check for myself.
What you will be interested in are the formulas in cell I2 and down

You will obviously need to change the cell references to suit your layout and I must add, these formulas will NOT work if there are blank cells in the range.

I'm sorry, but that is the best I can do, I hope it helps.

Good luck.

Ak

 

[magicbean]

Akashwani,

It worked great, thanks for your help! I am really appreciate it

 

[magicbean]

Akashwani,

i have tried the formula, it worked great. However, it did not count the cell that wrote as "PERMANENT" & "N.A"

 

[Akashwani]

Hi,

I'm pleased the formula worked for you, but I don't know what you mean by, "it did not count the cell that wrote as "PERMANENT" & "N.A" "

For me (or anyone else) to help you, you really must post some sample data or at least a clear explanation of your cell references and the data within the range.

Ak