Combine several conditions for count formula

[Tupelo1984]

Dear, I am looking for two count formulas that can combine several conditions. The first formula I need help on is:

Formula 1
condition 1/ Returns count '0' if a specific character appears in my lookup sheet 'lookup exclusions' (and counts 1 if character does not appear)
AND
2/ Returns count '0' if a specific character is missing in a 2nd lookup sheet 'lookup inclusions' (and counts 1i f character does not appear)

Desired result formula 1

SAMPLEDQ.xlsx
	A	B
1	Value	Result
2	!Eduard	0
3	%Kim	0
4	Hans	0
5	L	0
6	Tim	0
7	A\K|J	0
8	Hans@LED	1
9	Karen@LED%	0
Results formula 1

Lookup exclusions

SAMPLEDQ.xlsx
	A
1	EXCLUSIONS
2	!
3	#
4	##
5	~
6	%
7	^
8	&
9	*
10	(
11	|
12	\
13	)
14	_
15	+
lookup exclusions

lookup inclusions

SAMPLEDQ.xlsx
	A
1	inclusions
2	@
lookup inclusions

Formula 2
condition 1/ Returns count '0' if a specific character appears in my lookup sheet (and counts 1 if character does not appear)
AND
condition 2/ Returns count '0' if we have only 1 character in the cell (and counts 1 if more characters)

Desired result formula2

SAMPLEDQ.xlsx
	A	B
1	Value	Result
2	!Eduard	0
3	111Kim	0
4	Hans	0
5	L	0
6	Tim	1
7	A\K|J	0
Results formula 2

Lookup exclusions

SAMPLEDQ.xlsx
	A
1	EXCLUSIONS
2	!
3	#
4	##
5	~
6	%
7	^
8	&
9	*
10	(
11	|
12	\
13	)
14	_
15	+
lookup exclusions

I have already received help on the first condition: =(COUNT(0/FIND('lookup exclusions'!$A$2:$A$15,$A2))=0)*1
But am struggling to get in the second condition.

Thanks a lot for your help!

 

[DanteAmor]

Formula 1

How about:

Dante Amor
	A	B
1	Value	Result
2	!Eduard	0
3	%Kim	0
4	Hans	0
5	L	0
6	Tim	0
7	A\K|J	0
8	Hans@LED	1
9	Karen@LED%	0
results formula 1

Cell Formulas
Range	Formula
B2:B9	B2	=IF(IFERROR(LOOKUP(2,1/SEARCH("~"&'lookup exclusions'!$A$2:$A$15,A2),ROW('lookup exclusions'!$A$2:$A$15)*0),1)*IFERROR(LOOKUP(2,1/SEARCH("~"&'lookup inclusions'!$A$2:$A$2,A2),ROW('lookup inclusions'!$A$2:$A$2)*1),0),1,0)

 

[Tupelo1984]

Hi Dante, Thank you. I am not sure this is the expected result. I think I might have been unclear on my end.
I am looking for a formula with two conditions:
1/ That returns a 0 if any of the characters from 'lookup exclusions' is in the cell OR if the cell contains only one character (can be any character - nothing to do with the lookup exclusions)

If the cell doesn't contain any of my characters from 'lookup exclusions' or has more than one character than it should result in 1

Thanks for helping out - getting a bit desperate....

 

[Tupelo1984]

How about:

Dante Amor
	A	B
1	Value	Result
2	!Eduard	0
3	%Kim	0
4	Hans	0
5	L	0
6	Tim	0
7	A\K|J	0
8	Hans@LED	1
9	Karen@LED%	0
results formula 1

Cell Formulas
Range	Formula
B2:B9	B2	=IF(IFERROR(LOOKUP(2,1/SEARCH("~"&'lookup exclusions'!$A$2:$A$15,A2),ROW('lookup exclusions'!$A$2:$A$15)*0),1)*IFERROR(LOOKUP(2,1/SEARCH("~"&'lookup inclusions'!$A$2:$A$2,A2),ROW('lookup inclusions'!$A$2:$A$2)*1),0),1,0)

Hi Dante, Thank you. I am not sure this is the expected result. I think I might have been unclear on my end.
I am looking for a formula with two conditions:
1/ That returns a 0 if any of the characters from 'lookup exclusions' is in the cell OR if the cell contains only one character (can be any character - nothing to do with the lookup exclusions)

If the cell doesn't contain any of my characters from 'lookup exclusions' or has more than one character than it should result in 1

Thanks for helping out - getting a bit desperate....

Attachments​

• 
  
  
• 
  

 

[DanteAmor]

Formula 1
condition 1/ Returns count '0' if a specific character appears in my lookup sheet 'lookup exclusions' (and counts 1 if character does not appear)
AND
2/ Returns count '0' if a specific character is missing in a 2nd lookup sheet 'lookup inclusions' (and counts 1i f character does not appear)

Desired result formula 1

The formula that I gave you is for what you requested as "formula 1".
The "formula2" is pending.
Check if what I gave you for "formula 1" is what you need.