Tupelo1984
New Member
- Joined
- Jan 26, 2021
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
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
Lookup exclusions
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
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!
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!