Countif help

[excelexplorer]

Hi All,

I was trying to count the number of people in a range. The condition is that they should have blanks in a column d2 and as well as the conditional formatting of that cell is that the color is red. Is there any way where we can count the number of people who have blanks in cell d2 and as well as the cell color is red??

I am looking at the range of O2:O190 cells, So I am trying to frame a formula in the following way..

COUNTIF(O2:O190, IF(O2= " " && If color of cell is red.. ) can you please help me with the syntax?

Thanks,
Sam.

 

[Skybluekid]

Do you want to search on both conditions or is it either blank or red?

 

[naznorb]

I honestly don't know if it's possible to use conditional formatting states in formulas (someone here will know -- anyone?)

But it seems to me that the answer lies in the conditional formatting rules. Whether you used a formula for the conditional formatting or one of the native conditional formatting schemes, it can be recreated as an expression that will evaluate to true or false. That expression becomes the second part of your AND statement.

Hope that helps -- I might be able to provide more specifics if I knew what conditional formatting rules you were using.

cheers,
naz

 

[excelexplorer]

Yes, This is not only for one coloumn but most of the coloumns..

like example .. Here the we need the count of cells which are blank as well as red.. I mean here i am not able to quote the red color.. But yeah The cells which are blank are also red. Can you guys think of any formula.. for the below data we have the count as count(D) as 1

Count(A)=0 and count(B)=0 Count(C) =2
A B C D

0 2

2 3 5

 

[naznorb]

I'll have to bow out unless you have a sample workbook you'd like to upload -- I can't follow.

 

[excelexplorer]

C:\Users\prampall\Desktop\Book1.xlsx

I dont know how to link an excel file .. here can you please help?

 

[Skybluekid]

The syntax for what you want is

=countif(O2:O190,"")+countif(O2:O190,"[whatever else]")

However, there is no possible way to count formatting, as excel has nothing in the cell reference and whether to count it or not. The only thing that I can think of is:

1) Type the colour name in the cell(then use conditional formatting to colour the cell), then the formula would be

=countif(O2:O190,"")+countif(O2:O190,"red")

2) Use VBA to look at the formatting and then use the countif
(this is beyond my scope)

Hope this moves you forward a bit

 

[naznorb]

sorry ... forgot what msg. board i was on (attachments not allowed).

try sending a couple screenshots, perhaps including the conditional formatting rules you are using to color the cells red. Whatever they are, they can be reproduced using an expression.

You want the criteria to BOTH be met; i.e., the cell is blank and its color is red. You may be able to do it with a SUMIF, but I'd use a SUMIFS.

Let's say your data is in A1:A100. Let's say further that the condition for turning a cell red is something like the value of the adjacent cell in column B is <=2.0. Your formula is going to be:

=SUMIFS(A1:A100,A1:100,"",B1:B100,"<=2.0")

Depending on how complicated your conditional formatting is, you may need to make a dummy adjacent cell containing something that appears only if the same condition that made the cell red is met. Like

=IF(B1<=2.0,"a","")

Then you just make the second condition of your SUMIFS "a".

Best I can do....

 

[naznorb]

Actually even if you could just describe in words what makes the cells red, that might enable me to help you a bit better.

 

[excelexplorer]

=COUNTIFS($D17:$D180,"<>",E17:E180,"=")

Hi All,

This is the solution my friend came up with, But can anyone explain me the syntax or what this formula is doing?

Thanks,
sam.