Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
Hi. I must have missed a function somewhere. I have a range of numbers that
have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. TIA -- Phil |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
Hi. I must have missed a function somewhere. I have a range of numbers
that have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. Why not use the same condition that you used in the conditional format in a SUMPRODUCT to count the cells. For example, if your conditional format is based on cells, say for example, A1:A200 matching an input value in, say, B1, then use this... =SUMPRODUCT(--(A1:A200=B1)) or, depending on what your conditional actually is, something similar to the above. The point is, you don't have to search the range for the conditional formatting you set, just search for the cells that match the conditions you set in the Conditional Formatting... if that condition was sufficient to set the format, then it should be sufficient to be counted. Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
Don't count based on formatting. Count based on: "matching an input value."
But, we'd need the details! -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Hi. I must have missed a function somewhere. I have a range of numbers that have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. TIA -- Phil |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
Ok,
a1:a6 are input. Six numbers (1,2,3,4,5,6) b1:b6 is input from another range. (1,7,9,15,17,21,34,67,89) I need to count the matches. The obvious answer is 1 in this example. It's on the tip of my brain, teetering, just waiting to fall into place. "T. Valko" wrote: Don't count based on formatting. Count based on: "matching an input value." But, we'd need the details! -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Hi. I must have missed a function somewhere. I have a range of numbers that have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. TIA -- Phil |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
Ok, so which range do you want to count against? A1:A6 or B1:B6?
This will count matches in A1:A6 that appear in B1:B6 : =SUMPRODUCT(COUNTIF(B1:B6,A1:A6)) If I have it backwards just flip the ranges. -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Ok, a1:a6 are input. Six numbers (1,2,3,4,5,6) b1:b6 is input from another range. (1,7,9,15,17,21,34,67,89) I need to count the matches. The obvious answer is 1 in this example. It's on the tip of my brain, teetering, just waiting to fall into place. "T. Valko" wrote: Don't count based on formatting. Count based on: "matching an input value." But, we'd need the details! -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Hi. I must have missed a function somewhere. I have a range of numbers that have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. TIA -- Phil |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
I would bet a weeks pay that I tried that formula or a very close varient of
it last night before posting the question. When I noticed your answer I was working on a "count unique occurances" formula that would have subtracted them from the total numbers. Ugly, Clunky, etc. Thank you very much for saving my workbook from ugly and clunky. -- Phil "T. Valko" wrote: Ok, so which range do you want to count against? A1:A6 or B1:B6? This will count matches in A1:A6 that appear in B1:B6 : =SUMPRODUCT(COUNTIF(B1:B6,A1:A6)) If I have it backwards just flip the ranges. -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Ok, a1:a6 are input. Six numbers (1,2,3,4,5,6) b1:b6 is input from another range. (1,7,9,15,17,21,34,67,89) I need to count the matches. The obvious answer is 1 in this example. It's on the tip of my brain, teetering, just waiting to fall into place. "T. Valko" wrote: Don't count based on formatting. Count based on: "matching an input value." But, we'd need the details! -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Hi. I must have missed a function somewhere. I have a range of numbers that have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. TIA -- Phil |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT CELLS WITH CONDITIONAL FORMATTING
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... I would bet a weeks pay that I tried that formula or a very close varient of it last night before posting the question. When I noticed your answer I was working on a "count unique occurances" formula that would have subtracted them from the total numbers. Ugly, Clunky, etc. Thank you very much for saving my workbook from ugly and clunky. -- Phil "T. Valko" wrote: Ok, so which range do you want to count against? A1:A6 or B1:B6? This will count matches in A1:A6 that appear in B1:B6 : =SUMPRODUCT(COUNTIF(B1:B6,A1:A6)) If I have it backwards just flip the ranges. -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Ok, a1:a6 are input. Six numbers (1,2,3,4,5,6) b1:b6 is input from another range. (1,7,9,15,17,21,34,67,89) I need to count the matches. The obvious answer is 1 in this example. It's on the tip of my brain, teetering, just waiting to fall into place. "T. Valko" wrote: Don't count based on formatting. Count based on: "matching an input value." But, we'd need the details! -- Biff Microsoft Excel MVP "PEGWINN" wrote in message ... Hi. I must have missed a function somewhere. I have a range of numbers that have conditional formatting based on matching an input value. What I would like to do is count the cells that are conditionally formatted. I tried the count functions but cannot find an argument to identify formatting differences. TIA -- Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells that meetin conditional formatting criteria | Excel Worksheet Functions | |||
How can I count conditional blank cells? | Excel Worksheet Functions | |||
How do you count (not sum) cells containing special formatting? | Excel Worksheet Functions | |||
Conditional Formatting Multiple cells based on 2 cells | Excel Worksheet Functions | |||
Count and Sum with Conditional Formatting Problem | Excel Worksheet Functions |