Any ideas on how to shorten the following:-

Thanks

Sandy



=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,

K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,

T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U$ 8,0))

=IF(COUNTIF(C7:U7,0),0,COUNTIF($C$8:$K$8,0)+COUNTI F($M$8:$U$8,0))


"Sandy" wrote:

Any ideas on how to shorten the following:-

Thanks

Sandy



=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,

K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,

T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U$ 8,0))

One way

=IF(COUNTIF(C7:K7,0)+COUNTIF(M7:U7,0)<0,0,COUNTIF ($C$8:$K$8,0)+COUNTIF($M$8:$U$8,0))


--
Regards,

Peo Sjoblom




"Sandy" wrote in message
...
Any ideas on how to shorten the following:-

Thanks

Sandy



=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,

K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,

T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U$ 8,0))

Any ideas on how to shorten the following:-

=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,

K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,

T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U$ 8,0))

This maybe...

=IF(SUM(C7:K7,M7:U7)=0,0,COUNTIF($C$7:$K$7,0)+COUN TIF($M$7:$U$7,0))

Rick

=if(product(C7:K7,M7:U7)=0,0,COUNTIF($C$8:$K$8,0)+ COUNTIF($M$8:$U$8,0))





"Sandy" wrote:

Any ideas on how to shorten the following:-

Thanks

Sandy



=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,

K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,

T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U$ 8,0))

=if(product(C7:K7,M7:U7)=0,0,COUNTIF($C$8:$K$8,0)+ COUNTIF($M$8:$U$8,0))

If any one of them is 0, won't the product be 0 no matter what number is in
any of the others?

Rick

Wasn't that what the OP wanted since he used OR, meaning that if a single
one of those cells is 0 then return 0? I believe that is where your formula
fails.


--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
=if(product(C7:K7,M7:U7)=0,0,COUNTIF($C$8:$K$8,0)+ COUNTIF($M$8:$U$8,0))

If any one of them is 0, won't the product be 0 no matter what number is
in any of the others?

Rick

Wasn't that what the OP wanted since he used OR, meaning that
if a single one of those cells is 0 then return 0? I believe that is
where your formula fails.

Hmm! I saw OR and got AND locked into my brain somehow.

Rick

and another


=IF(ABS(MIN(C7:K7,M7:U7))=0,0,COUNTIF($C$8:$K$8,0) +COUNTIF($M$8:$U$8,0))

Mike
"Sandy" wrote:

Any ideas on how to shorten the following:-

Thanks

Sandy



=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,

K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,

T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U$ 8,0))

"Sandy" wrote...
Any ideas on how to shorten the following:-
....
=IF(OR(C7=0,D7=0,E7=0,F7=0,G7=0,H7=0,I7=0,J7=0,
K7=0,M7=0,N7=0,O7=0,P7=0,Q7=0,R7=0,S7=0,
T7=0,U7=0),0,COUNTIF($C$8:$K$8,0)+COUNTIF($M$8:$U $8,0))

=SIGN(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))

"Harlan Grove" wrote in message
oups.com...
"Sandy" wrote...

=SIGN(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))


For me, & FWIW, your formula does not return the same as the OP:

C7:U7 all non-zero numbers, C8:U8 all 0's
OP returns 18
Harlan returns 0

Change L7 to en empty cell:
OP returns 18
Harlan returns -18

Change L7 to 0
OP returns 18
Harlan returns 0

Change any other cell in C7:U7 to 0
OP Returns 0
Harlan returns 18

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

=SIGN(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))


For me, & FWIW, your formula does not return the same as the OP:

Perhaps Harlan accidentally omitted an '=0' from his equation (thinking it
was already in there when he saw the '=0' from 'L7=0'). This modification to
his formula seems to work...

=SIGN(COUNTIF(C7:U7,0)-(L7=0)=0)*(COUNTIF($C$8:$U$8,0)-($L$8=0))

Rick

"Rick Rothstein (MVP - VB)" wrote in
message ...

Perhaps Harlan accidentally omitted an '=0' from his equation

Drat! I wish I had thought of that - I would love to have corrected one of
Harlan's formulas <g

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Rick Rothstein (MVP - VB)" wrote in
message ...
=SIGN(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))


For me, & FWIW, your formula does not return the same as the OP:

Perhaps Harlan accidentally omitted an '=0' from his equation (thinking it
was already in there when he saw the '=0' from 'L7=0'). This modification
to his formula seems to work...

=SIGN(COUNTIF(C7:U7,0)-(L7=0)=0)*(COUNTIF($C$8:$U$8,0)-($L$8=0))

Rick

"Rick Rothstein \(MVP - VB\)" wrote...
=SIGN(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))
....

I screwed up on that one.

. . . This modification to his formula seems to work...

=SIGN(COUNTIF(C7:U7,0)-(L7=0)=0)*(COUNTIF($C$8:$U$8,0)-($L$8=0))

It may, but what I should have written was

=NOT(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))

. . . This modification to his formula seems to work...

=SIGN(COUNTIF(C7:U7,0)-(L7=0)=0)*(COUNTIF($C$8:$U$8,0)-($L$8=0))

It may, but what I should have written was

=NOT(COUNTIF(C7:U7,0)-(L7=0))*(COUNTIF($C$8:$U$8,0)-($L$8=0))

They are equivalent...

SIGN(FALSE) = SIGN(0) = NOT(1)

SIGN(TRUE) = SIGN(1) = NOT(0)

Whether using the SIGN or NOT approach, I like the overall construction of
the formula... especially the

COUNTIF(C7:U7,0)-(L7=0)

construction which guarantees that it can't evaluate to the troublesome -1
value that would cause NOT(-1) to be a problem. Nice insight there Harlan.

Rick