Countif help

excelexplorer

New Member
Joined
Apr 30, 2012
Messages
23
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Do you want to search on both conditions or is it either blank or red?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I'll have to bow out unless you have a sample workbook you'd like to upload -- I can't follow.
 
Upvote 0
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 ;)
 
Upvote 0
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....
 
Upvote 0
Actually even if you could just describe in words what makes the cells red, that might enable me to help you a bit better.
 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,212,141
Messages
6,106,204
Members
448,005
Latest member
Valvictor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top