Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Complicated conditional "countif" formula
Hi. I have several columns labeled "Monitoring Visit 1...Monitoring
Visit 8", but interspersed between them I have columns labeled "Report Date", with each report being associated with a given Monitoring visit. In the last column, on each row, I want to count the number of cells with dates in them, which signifies if a monitoring visit has occurred or not. I can't do a normal count if, because of the extra Report columns in between, which have stay where they are. Can anyone lead me to the right formula? Thanks. |
#2
|
|||
|
|||
=SUMPRODUCT(--(LEFT(A$1:O$1,16)="Monitoring Visit"),--(A2:O2<""))
-- HTH RP (remove nothere from the email address if mailing direct) "bruch04" wrote in message oups.com... Hi. I have several columns labeled "Monitoring Visit 1...Monitoring Visit 8", but interspersed between them I have columns labeled "Report Date", with each report being associated with a given Monitoring visit. In the last column, on each row, I want to count the number of cells with dates in them, which signifies if a monitoring visit has occurred or not. I can't do a normal count if, because of the extra Report columns in between, which have stay where they are. Can anyone lead me to the right formula? Thanks. |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(--(MOD(COLUMN(A2:P2)-COLUMN(A2),2)=0),--(ISNUMBER(A2:P2))) Hope this helps! In article .com, "bruch04" wrote: Hi. I have several columns labeled "Monitoring Visit 1...Monitoring Visit 8", but interspersed between them I have columns labeled "Report Date", with each report being associated with a given Monitoring visit. In the last column, on each row, I want to count the number of cells with dates in them, which signifies if a monitoring visit has occurred or not. I can't do a normal count if, because of the extra Report columns in between, which have stay where they are. Can anyone lead me to the right formula? Thanks. |
#4
|
|||
|
|||
Thanks very much for your replies. In the meantime, I've decided to
just format the Monitoring visit columns with a different fill color and count based on that, but now I have a problem that my UDF is counting all the colored columns, not just the nonblank ones. Please be patient with me, I'm pretty new at excel.... My function is called countbycolor. Can I combine this function with a normal counta or countif to come up with only nonblank, colored cells? Thanks. |
#5
|
|||
|
|||
I would advise against this, the previous way is much more resilient.
-- HTH RP (remove nothere from the email address if mailing direct) "bruch04" wrote in message oups.com... Thanks very much for your replies. In the meantime, I've decided to just format the Monitoring visit columns with a different fill color and count based on that, but now I have a problem that my UDF is counting all the colored columns, not just the nonblank ones. Please be patient with me, I'm pretty new at excel.... My function is called countbycolor. Can I combine this function with a normal counta or countif to come up with only nonblank, colored cells? Thanks. |
#6
|
|||
|
|||
Okay, I'll try to stick with the first method, but oddly, the formula
you provided above is being evaluated as a constant, even with the "=" in front of it. I can't even get to the formula evaluation stage to audit it, because it's a "constant". ??? |
#7
|
|||
|
|||
Sounds like you have formulas switched off. Goto ToolsOptionsView, and
make sure Formulas is not checked. -- HTH RP (remove nothere from the email address if mailing direct) "bruch04" wrote in message oups.com... Okay, I'll try to stick with the first method, but oddly, the formula you provided above is being evaluated as a constant, even with the "=" in front of it. I can't even get to the formula evaluation stage to audit it, because it's a "constant". ??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting formula not acceptable? | Excel Discussion (Misc queries) | |||
Using a Formula in a Conditional Statement? | Excel Worksheet Functions | |||
COUNTIF formula problems | Excel Discussion (Misc queries) | |||
conditional formatting formula | Excel Discussion (Misc queries) | |||
Formula Dependant Conditional Formatting | Excel Discussion (Misc queries) |