If any 3 of 5 values are 0 then 0.
 

Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

Hi,

There's bound to be a better way but this works if there are no negative
values:-


=IF(SMALL(Myrange,3)=0,0,SUM(Myrange))

I've used a named range 'Myrange' for your 5 cells.

Mike

"Gary" wrote:

Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

=IF(OR((MOD(G7:O7,2)=1)*(COUNTIF(G7:O7,0)=3),O7=0 ),0,SUM(G7:O7))


"Gary" wrote:

Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

Make sure press Ctrl+Shift+Enter, not just enter in my early replied


"Teethless mama" wrote:

=IF(OR((MOD(G7:O7,2)=1)*(COUNTIF(G7:O7,0)=3),O7=0 ),0,SUM(G7:O7))


"Gary" wrote:

Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

=IF(OR(O7=0,SUMPRODUCT(--(MOD(COLUMN(G7:O7),2)=1),--(G7:O7=0))=3),0,
SUMPRODUCT(--(MOD(COLUMN(G7:O7),2)=1),G7:O7))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gary" wrote in message
...
Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like
this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

Should you be MODing the column, and summing the MOD columns in case any
others have values

=IF(OR((MOD(COLUMN(G7:O7),2)=1)*(COUNTIF(G7:O7,0) =3),O7=0),0,SUM(IF(MOD(COLUMN(G7:O7),2)=1,G7:O7)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Teethless mama" wrote in message
...
=IF(OR((MOD(G7:O7,2)=1)*(COUNTIF(G7:O7,0)=3),O7=0 ),0,SUM(G7:O7))


"Gary" wrote:

Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like
this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

Thanks Everyone

"Gary" wrote in message
...
Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like
this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

If any 3 of 5 values are 0 then 0.
 

On Oct 31, 9:12 am, "Gary" wrote:
Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.

Try...

=IF(O7=0,0,IF(INDEX(FREQUENCY((G7,I7,K7,M7,O7),{0. 999999999999999,0}),
2)=3,0,SUM(G7,I7,K7,M7,O7)))

Hope this helps!

If any 3 of 5 values are 0 then 0.
 

I don't know how much better this is than what's already been
suggested, but you can try:

=--AND(((O17=0)+(M17=0)+(K17=0)+(I17=0)+(G17=0))<3,O1 7<0)

It returns one if neither 0 condition is met, so you can multiply it
by another value to get a different result. For instance, say that
formula is in A1, you can then say in A2:

=AVERAGE(B2:B10)*A1

and that will return the average if neither 017 is 0 nor more than 2
others are 0. Otherwise, it will show 0.


On Oct 31, 9:12 am, "Gary" wrote:
Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0, "") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.