Long formula

[Sandy]

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

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[Rick Rothstein \(MVP - VB\)]

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)+COUNTIF($M$7:$U$7,0))

Rick

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

=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

 

[Guest]

and another


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

Mike

 

[Peo Sjoblom]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Harlan Grove]

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

 

[Sandy Mann]

"Sandy" <[email protected]> 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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

=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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Harlan Grove]

.......

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Harlan Grove]

...
....

They are equivalent...

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

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

Yeah, but the second is 3 chars shorter.

 

[Rick Rothstein \(MVP - VB\)]

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

...

Yeah, but the second is 3 chars shorter.

LOL... yeah, I have to give you that; but remember, I was patching what you
posted, not what you were thinking.<g>

Rick

 

[Sandy]

Lost me with the last few but thanks all!
Sandy

LOL... yeah, I have to give you that; but remember, I was patching what
you posted, not what you were thinking.<g>

Rick