=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2*(COUNTIF(A6:E7, 1)+COUNTIF(A11:E12,1)+COUNTIF(A16:E17,1)+COUNTIF(A 21:E22,1)+COUNTIF(A26:E27,1)+COUNTIF(A31:E32,1)+CO UNTIF(A36:E37,1)+COUNTIF(A41:E42,1)=1),1,""),""))

The above formula is returning #Value.

I am trying to find if A1=1, then look for only two occurrences of the
number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
(A6:E7)+(A11:E12)+(A16:E17)+(A21:E22)+(A26:E27)+(A 31:E32)+(A36:E37)+(A41:E42),
for only one occurrence of the number 1. If either the first one range group
or the second eight range groups are true, return the number 1 or leave the
cell blank.

I tried to make this a question as clear as I could. I would appreciate any
help.

=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2,SUM(COUNTIF(A6: E7,1),COUNTIF(A21:E22,1),.......)=1),1,""),"")

Expand the range within sum in the formula aboove where you see .....by
including other countif's I only entered 2 just to show it to you


"Cliff" wrote:

=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2*(COUNTIF(A6:E7, 1)+COUNTIF(A11:E12,1)+COUNTIF(A16:E17,1)+COUNTIF(A 21:E22,1)+COUNTIF(A26:E27,1)+COUNTIF(A31:E32,1)+CO UNTIF(A36:E37,1)+COUNTIF(A41:E42,1)=1),1,""),""))

The above formula is returning #Value.

I am trying to find if A1=1, then look for only two occurrences of the
number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
(A6:E7)+(A11:E12)+(A16:E17)+(A21:E22)+(A26:E27)+(A 31:E32)+(A36:E37)+(A41:E42),
for only one occurrence of the number 1. If either the first one range group
or the second eight range groups are true, return the number 1 or leave the
cell blank.

I tried to make this a question as clear as I could. I would appreciate any
help.

Thanks for your help, but it appears not to be working.

When I enter the formula in cell A4, I have to add an additional ) at
the very end to accept the entry and when I run it with blank ranges, I get
False in the cell. When I enter a 1 in A1 and/or any of the cells I get
#Value.

"N Harkawat" wrote:

=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2,SUM(COUNTIF(A6: E7,1),COUNTIF(A21:E22,1),.......)=1),1,""),"")

Expand the range within sum in the formula aboove where you see .....by
including other countif's I only entered 2 just to show it to you


"Cliff" wrote:

=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2*(COUNTIF(A6:E7, 1)+COUNTIF(A11:E12,1)+COUNTIF(A16:E17,1)+COUNTIF(A 21:E22,1)+COUNTIF(A26:E27,1)+COUNTIF(A31:E32,1)+CO UNTIF(A36:E37,1)+COUNTIF(A41:E42,1)=1),1,""),""))

The above formula is returning #Value.

I am trying to find if A1=1, then look for only two occurrences of the
number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
(A6:E7)+(A11:E12)+(A16:E17)+(A21:E22)+(A26:E27)+(A 31:E32)+(A36:E37)+(A41:E42),
for only one occurrence of the number 1. If either the first one range group
or the second eight range groups are true, return the number 1 or leave the
cell blank.

I tried to make this a question as clear as I could. I would appreciate any
help.

I got the correction and it worked. It should be .....))=1),1,""),"").
Thanks again.
--
Cliff


"Cliff" wrote:

Thanks for your help, but it appears not to be working.

When I enter the formula in cell A4, I have to add an additional ) at
the very end to accept the entry and when I run it with blank ranges, I get
False in the cell. When I enter a 1 in A1 and/or any of the cells I get
#Value.

"N Harkawat" wrote:

=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2,SUM(COUNTIF(A6: E7,1),COUNTIF(A21:E22,1),.......)=1),1,""),"")

Expand the range within sum in the formula aboove where you see .....by
including other countif's I only entered 2 just to show it to you


"Cliff" wrote:

=IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2*(COUNTIF(A6:E7, 1)+COUNTIF(A11:E12,1)+COUNTIF(A16:E17,1)+COUNTIF(A 21:E22,1)+COUNTIF(A26:E27,1)+COUNTIF(A31:E32,1)+CO UNTIF(A36:E37,1)+COUNTIF(A41:E42,1)=1),1,""),""))

The above formula is returning #Value.

I am trying to find if A1=1, then look for only two occurrences of the
number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
(A6:E7)+(A11:E12)+(A16:E17)+(A21:E22)+(A26:E27)+(A 31:E32)+(A36:E37)+(A41:E42),
for only one occurrence of the number 1. If either the first one range group
or the second eight range groups are true, return the number 1 or leave the
cell blank.

I tried to make this a question as clear as I could. I would appreciate any
help.