Count if within conditional formatted cells

[kmaki]

How do I countif where cells are conditionally formatted to either "yes"
(formatted to change fill to yellow) or "No'(formatted to change fill to
orange"

The end result is for the # of yes and the # of nos.

Thank you.

 

[macropod]

Hi kmaki,

There is no worksheet function to count cells based on their colour. You'll need to build your formula using the same logic that you
used to determine what colour the cells should be.

Cheers

 

[kmaki]

Thanks for reply. The formula I tried is
=countif(b8:b12,"X")+countif(b17:b21,"X") or
countif(b8:b12,"No")+countif(b17:b21."No")

Hi kmaki,

There is no worksheet function to count cells based on their colour. You'll need to build your formula using the same logic that you
used to determine what colour the cells should be.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How do I countif where cells are conditionally formatted to either "yes"
(formatted to change fill to yellow) or "No'(formatted to change fill to
orange"

The end result is for the # of yes and the # of nos.

Thank you.

 

[Gord Dibben]

=COUNTIF(Range,"yes")

=COUNTIF(Range,"no")

To count manually-colored cells requires VBA and to count cells colored by CF
requires much more VBA.


Gord Dibben MS Excel MVP

 

[kmaki]

Thank you Gord. One more question, this sheet that I am using, has data in 5
consecutive rows, and skips 4 rows in between, will the same
countif(b8:b12,B17:b21,"yes") +countif(b8:b12,B17:b21,"no") produce a correct
sum?

 

[macropod]

Hi kmaki,

You could use an array formula like:
=SUM(IF((B8:B12="X")+(B17:B21="X"),1))
With array formulae, you complete their entry with Ctrl-Shift-Enter, rather than just Enter.

--
macropod
[MVP - Microsoft Word]
-------------------------

Thanks for reply. The formula I tried is
=countif(b8:b12,"X")+countif(b17:b21,"X") or
countif(b8:b12,"No")+countif(b17:b21."No")

Hi kmaki,

There is no worksheet function to count cells based on their colour. You'll need to build your formula using the same logic that
you
used to determine what colour the cells should be.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How do I countif where cells are conditionally formatted to either "yes"
(formatted to change fill to yellow) or "No'(formatted to change fill to
orange"

The end result is for the # of yes and the # of nos.

Thank you.

 

[AdamV]

no, that's not even a valid formula, you need to use something like
countif(b8:b12,"yes") +countif(B17:b21,"yes")+countif(b8:b12,"no")
+countif(B17:b21,"no")

 

[kmaki]

Thanks Adam, I was trying to get to the final number sooner than to have to
type the formulas as you mentioned.

 

[TheUbe]

I use a spreadsheet to record answers to a test. Each cell the answer is
recorded in looks to another cell with the correct answer. All wrong answers
cond. format to red background. Is there no easy way to have Excel count how
many cells are being conditionally formated to give me their score? I tried
http://www.xldynamic.com/source/xld.ColourCounter.html#counting but my cell
value always returns #name? I'm using Excel 2007.
--
Ken

Hi kmaki,

There is no worksheet function to count cells based on their colour. You'll need to build your formula using the same logic that you
used to determine what colour the cells should be.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How do I countif where cells are conditionally formatted to either "yes"
(formatted to change fill to yellow) or "No'(formatted to change fill to
orange"

The end result is for the # of yes and the # of nos.

Thank you.

 

[T. Valko]

Instead of counting color formatted cells count the cells based on:

Each cell the answer is recorded in looks to
another cell with the correct answer.

If A1:A10 are the answer cells and the answer key (the correct answers) is
in the range AA1:AA10 -

=SUMPRODUCT(--(A1:A10<>AA1:AA10)

--
Biff
Microsoft Excel MVP

I use a spreadsheet to record answers to a test. Each cell the answer is
recorded in looks to another cell with the correct answer. All wrong
answers
cond. format to red background. Is there no easy way to have Excel count
how
many cells are being conditionally formated to give me their score? I
tried
http://www.xldynamic.com/source/xld.ColourCounter.html#counting but my
cell
value always returns #name? I'm using Excel 2007.
--
Ken

Hi kmaki,

There is no worksheet function to count cells based on their colour.
You'll need to build your formula using the same logic that you
used to determine what colour the cells should be.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How do I countif where cells are conditionally formatted to either
"yes"
(formatted to change fill to yellow) or "No'(formatted to change fill
to
orange"

The end result is for the # of yes and the # of nos.

Thank you.

 

[TheUbe]

That worked perfectly with a simple mod changing <> to =

BTW I've seen these formulas before with the (--( What does the outer
parenthesis and dashes do for the formula?

--
Ken

Instead of counting color formatted cells count the cells based on:

Each cell the answer is recorded in looks to
another cell with the correct answer.

If A1:A10 are the answer cells and the answer key (the correct answers) is
in the range AA1:AA10 -

=SUMPRODUCT(--(A1:A10<>AA1:AA10)

--
Biff
Microsoft Excel MVP

I use a spreadsheet to record answers to a test. Each cell the answer is
recorded in looks to another cell with the correct answer. All wrong
answers
cond. format to red background. Is there no easy way to have Excel count
how
many cells are being conditionally formated to give me their score? I
tried
http://www.xldynamic.com/source/xld.ColourCounter.html#counting but my
cell
value always returns #name? I'm using Excel 2007.
--
Ken

Hi kmaki,

There is no worksheet function to count cells based on their colour.
You'll need to build your formula using the same logic that you
used to determine what colour the cells should be.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How do I countif where cells are conditionally formatted to either
"yes"
(formatted to change fill to yellow) or "No'(formatted to change fill
to
orange"

The end result is for the # of yes and the # of nos.

Thank you.

 

[TheUbe]

Thank you very much.
--
Ken

Google for "double unary minus".
--
David Biddulph

That worked perfectly with a simple mod changing <> to =

BTW I've seen these formulas before with the (--( What does the outer
parenthesis and dashes do for the formula?

--
Ken

Instead of counting color formatted cells count the cells based on:

Each cell the answer is recorded in looks to
another cell with the correct answer.

If A1:A10 are the answer cells and the answer key (the correct answers)
is
in the range AA1:AA10 -

=SUMPRODUCT(--(A1:A10<>AA1:AA10)

--
Biff
Microsoft Excel MVP


I use a spreadsheet to record answers to a test. Each cell the answer
is
recorded in looks to another cell with the correct answer. All wrong
answers
cond. format to red background. Is there no easy way to have Excel
count
how
many cells are being conditionally formated to give me their score? I
tried
http://www.xldynamic.com/source/xld.ColourCounter.html#counting but my
cell
value always returns #name? I'm using Excel 2007.
--
Ken


:

Hi kmaki,

There is no worksheet function to count cells based on their colour.
You'll need to build your formula using the same logic that you
used to determine what colour the cells should be.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How do I countif where cells are conditionally formatted to either
"yes"
(formatted to change fill to yellow) or "No'(formatted to change
fill
to
orange"

The end result is for the # of yes and the # of nos.

Thank you.