If any 3 of 5 values are 0 then 0.

[Gary]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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

 

[Bob Phillips]

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)

 

[Gary]

Thanks Everyone

 

[domenic22]

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!

 

[ilia]

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,O17<>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.