Question about Data Validation.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,834
Office Version
2019
Platform
Windows
oki but please check post 19 as we both kind of posted at the same time.. appreciate your efforts a lot.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,834
Office Version
2019
Platform
Windows
=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. I think I got this. Had to alter from this <>0 into this >0
I thank you for this AlanY.
t c.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
try

Book1
A
1TRUE
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))=COUNTIF(A3:A10,">0")))
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,834
Office Version
2019
Platform
Windows
try

Book1
A
1TRUE
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))=COUNTIF(A3:A10,">0")))
Dear Alan,

In your post #17 you had come closest to achieving my requirements and by removing this "<" bracket I was able to get all of my requirements in post #2 fulfilled. For example,

Your Formula from post #17
=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)))
After removing the underlined bracket from the end of the formula
=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)))
Sorry to have repeat this as I've already mentioned this in my last post to you..
Thanks again...
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
still not quite right though
this gives correct answer

Book1
A
1FALSE
2C
3
41
5
6-1
7
8
9
10
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))=COUNTIF(A3:A10,">0")))


but this isn't

Book1
A
1TRUE
2C
3
41
5
6-1
7
8
9
10
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))=COUNTIF(A3:A10,">0")))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,856
Messages
5,507,735
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top