software

=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,quot;quot;),quot;quot;))

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,quot;quot;),quot;quot;)

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 youquot;Cliffquot; wrote:

gt; =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,quot;quot;),quot;quot;))
gt;
gt; The above formula is returning #Value.
gt;
gt; I am trying to find if A1=1, then look for only two occurrences of the
gt; number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
gt; (A6:E7) (A11:E12) (A16:E17) (A21:E22) (A26:E27) (A 31:E32) (A36:E37) (A41:E42),
gt; for only one occurrence of the number 1. If either the first one range group
gt; or the second eight range groups are true, return the number 1 or leave the
gt; cell blank.
gt;
gt; I tried to make this a question as clear as I could. I would appreciate any
gt; 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.

quot;N Harkawatquot; wrote:

gt; =IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2,SUM(COUNTIF(A6: E7,1),COUNTIF(A21:E22,1),.......)=1),1,quot;quot;),quot;quot;)
gt;
gt; Expand the range within sum in the formula aboove where you see .....by
gt; including other countif's I only entered 2 just to show it to you
gt;
gt;
gt; quot;Cliffquot; wrote:
gt;
gt; gt; =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,quot;quot;),quot;quot;))
gt; gt;
gt; gt; The above formula is returning #Value.
gt; gt;
gt; gt; I am trying to find if A1=1, then look for only two occurrences of the
gt; gt; number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
gt; gt; (A6:E7) (A11:E12) (A16:E17) (A21:E22) (A26:E27) (A 31:E32) (A36:E37) (A41:E42),
gt; gt; for only one occurrence of the number 1. If either the first one range group
gt; gt; or the second eight range groups are true, return the number 1 or leave the
gt; gt; cell blank.
gt; gt;
gt; gt; I tried to make this a question as clear as I could. I would appreciate any
gt; gt; help.

I got the correction and it worked. It should be .....))=1),1,quot;quot;),quot;quot;).
Thanks again.
--
Cliffquot;Cliffquot; wrote:

gt; Thanks for your help, but it appears not to be working.
gt;
gt; When I enter the formula in cell A4, I have to add an additional ) at
gt; the very end to accept the entry and when I run it with blank ranges, I get
gt; False in the cell. When I enter a 1 in A1 and/or any of the cells I get
gt; #Value.
gt;
gt; quot;N Harkawatquot; wrote:
gt;
gt; gt; =IF(A1=1,IF(OR(COUNTIF(A1:AS2,1)=2,SUM(COUNTIF(A6: E7,1),COUNTIF(A21:E22,1),.......)=1),1,quot;quot;),quot;quot;)
gt; gt;
gt; gt; Expand the range within sum in the formula aboove where you see .....by
gt; gt; including other countif's I only entered 2 just to show it to you
gt; gt;
gt; gt;
gt; gt; quot;Cliffquot; wrote:
gt; gt;
gt; gt; gt; =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,quot;quot;),quot;quot;))
gt; gt; gt;
gt; gt; gt; The above formula is returning #Value.
gt; gt; gt;
gt; gt; gt; I am trying to find if A1=1, then look for only two occurrences of the
gt; gt; gt; number 1 in cell range A1:AS2 and then look in these eight ranges as a group,
gt; gt; gt; (A6:E7) (A11:E12) (A16:E17) (A21:E22) (A26:E27) (A 31:E32) (A36:E37) (A41:E42),
gt; gt; gt; for only one occurrence of the number 1. If either the first one range group
gt; gt; gt; or the second eight range groups are true, return the number 1 or leave the
gt; gt; gt; cell blank.
gt; gt; gt;
gt; gt; gt; I tried to make this a question as clear as I could. I would appreciate any
gt; gt; gt; help.