Complicated conditional "countif" formula

B

bruch04

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

Bob Phillips

=SUMPRODUCT(--(LEFT(A$1:O$1,16)="Monitoring Visit"),--(A2:O2<>""))

--

HTH

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

Domenic

Try...

=SUMPRODUCT(--(MOD(COLUMN(A2:p2)-COLUMN(A2),2)=0),--(ISNUMBER(A2:p2)))

Hope this helps!
 
B

bruch04

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

Bob Phillips

I would advise against this, the previous way is much more resilient.

--

HTH

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

bruch04

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

Bob Phillips

Sounds like you have formulas switched off. Goto Tools>Options>View, and
make sure Formulas is not checked.

--

HTH

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

bruch04

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 Tools>Options>View and it is not
checked.
 
B

bruch04

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,
 
B

Bob Phillips

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)
 
Top