| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if within conditional formatted cells
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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] ------------------------- "kmaki" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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") "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
=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 On Sun, 13 Apr 2008 16:17:00 -0700, kmaki wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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? "Gord Dibben" wrote: =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 On Sun, 13 Apr 2008 16:17:00 -0700, kmaki wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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] ------------------------- "kmaki" wrote in message ... 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") "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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" wrote: 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? "Gord Dibben" wrote: =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 On Sun, 13 Apr 2008 16:17:00 -0700, kmaki wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
Thanks Adam, I was trying to get to the final number sooner than to have to
type the formulas as you mentioned. "AdamV" wrote: 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" wrote: 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? "Gord Dibben" wrote: =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 On Sun, 13 Apr 2008 16:17:00 -0700, kmaki wrote: 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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.....html#counting but my cell value always returns #name? I'm using Excel 2007. -- Ken "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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 "TheUbe" wrote in message ... 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.....html#counting but my cell value always returns #name? I'm using Excel 2007. -- Ken "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
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 "T. Valko" wrote: 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 "TheUbe" wrote in message ... 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.....html#counting but my cell value always returns #name? I'm using Excel 2007. -- Ken "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
Google for "double unary minus".
-- David Biddulph "TheUbe" wrote in message ... 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 "T. Valko" wrote: 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 "TheUbe" wrote in message ... 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.....html#counting but my cell value always returns #name? I'm using Excel 2007. -- Ken "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if within conditional formatted cells
Thank you very much.
-- Ken "David Biddulph" wrote: Google for "double unary minus". -- David Biddulph "TheUbe" wrote in message ... 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 "T. Valko" wrote: 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 "TheUbe" wrote in message ... 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.....html#counting but my cell value always returns #name? I'm using Excel 2007. -- Ken "macropod" wrote: 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] ------------------------- "kmaki" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| XL2003: Count formatted cells (not coloured)... | Excel Worksheet Functions | |||
| Return Min & Max value of Conditional Formatted cells | Excel Worksheet Functions | |||
| Can I count conditionally formatted red cells in Excel 2000 | Excel Discussion (Misc queries) | |||
| How to count the number of Excel cells with text formatted Italic | Excel Worksheet Functions | |||
| How do i count the number of conditional formatted cells? | Excel Discussion (Misc queries) | |||
Linear Mode
