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.

=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.

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.

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.

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.

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". ???

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". ???

I know it sounds strange, but I have other formulas in other cells that
are working fine.

Just to make sure though, I went to ToolsOptionsView and it is not
checked.

AHA! Okay, I got it to work. I ended up using this formula:

=sumproduct((Left($A$1:$A$7,10)="Monitoring")*$A2: $F7<""))

I gave up on the colour formatting after reading your sumproduct page.
Thanks very much for your help.

Kind regards,

It is odd that that one should work and the previous one didn't, they are
the same, the comparison string and the operator apart.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bruch04" wrote in message
oups.com...
AHA! Okay, I got it to work. I ended up using this formula:

=sumproduct((Left($A$1:$A$7,10)="Monitoring")*$A2: $F7<""))

I gave up on the colour formatting after reading your sumproduct page.
Thanks very much for your help.

Kind regards,