#### omairhe

##### Well-known Member
if this still didn't work, I'm done
No hard feelings. You did OK. Thanks anyways love.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### AlanY

##### Well-known Member
last try

Book1
A
1TRUE
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

#### AlanY

##### Well-known Member
The formula is giving me a FALSE. I cannot make the formula TRUE no matter what I type in the range or in cell A2.
Please I would like you to make an effort for a formula that can adapt to various Range sizes. Currently the range is not big. For instance A3:A10, in other sheets the range is A3:A40 and many other sheets where range is even larger. All need to have your formula in them. I see an 8 at the end and I assume I will have to manually change it for other ranges? Correct?
try post#12 below

yes 8 can be dynamic as count(A3:A8)

#### omairhe

##### Well-known Member
try post#12 below

yes 8 can be dynamic as count(A3:A8)
same result. unable to get a TRUE no matter what I type in. Tried both Ctrl+Shift+Enter eg. {} and without the curly brackets.

#### AlanY

##### Well-known Member
have a look of this test
try to copy below to see if you can produce same results

Book1
A
1TRUE
2B
31
41
51
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

Book1
A
1FALSE
2A
31
41
51.1
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

Book1
A
1TRUE
2A
31
41
55
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

Book1
A
1FALSE
2
31
41
55
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

#### omairhe

##### Well-known Member
have a look of this test
try to copy below to see if you can produce same results

Book1
A
1TRUE
2B
31
41
51
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

Book1
A
1FALSE
2A
31
41
51.1
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

Book1
A
1TRUE
2A
31
41
55
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

Book1
A
1FALSE
2
31
41
55
6-1
7-1
8-1
91
10-1
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))

I was testing your formulas on empty range. for instance A3:A10 was empty . so I put A3 = 5 and A2 = A , it was FALSE.
Now that I have all the cells in range A3:A10 filled up with data. I am getting the same result as you are having.
How do I allow empty cells in range A3:A10 without affecting the formula result?
Thanks

Edit: if all cells in range are empty this should give TRUE. because at least A2 is not empty or not an A. If A2 was to remain empty then this should give FALSE.
Hence the first condition is for A2 to have either an A or anything other than A. (B, C or D etc)

#### AlanY

##### Well-known Member
this may work

Book1
A
1TRUE
2B
3
4
5
6
7
8-1
91
10-1
Sheet1
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNT(A3:A10)))

• omairhe

#### omairhe

##### Well-known Member
this may work

Book1
A
1TRUE
2B
3
4
5
6
7
8-1
91
10-1
Sheet1
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNT(A3:A10)))
Yes, very very close. Only two requirements remain now. Please don't give up now.. hehe

IF A2 = A and range A3:A10 consists of negative integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of both positive and negative integer/s then A1 = TRUE

IF A2 = not blank and range A3:A10 consists of negative integer/s then A1 = FALSE
IF A2 = not blank and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of both positive and negative integer/s then A1 = FALSE
IF A2 = A or not blank and range A3:A10 consists of exact zero "0" value then A1 = FALSE
IF A2 = blank then A1 = FALSE
For instance when A2 = B

#### omairhe

##### Well-known Member
only A should allow negative integers along with positive. if any.
however, any other value other than A in A2 should only allow positive numbers.

#### AlanY

##### Well-known Member
Yes, very very close. Only two requirements remain now. Please don't give up now.. hehe

For instance when A2 = B
I’m away from my laptop now, will have another go when I’m back

• omairhe