Description
I have a table like this in Google Sheet:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Cond1 | Person_code | n/a | Count | Cond2 | n/a | Result |
| __ | _______ | ________________ | _____ | ________ | _______ | _____ | ________ |
| 2 | 0 | Tom T_44767 | 1 | 1 | |||
| 3 | 0 | Isrel I_44767 | 1 | 1 | |||
| 4 | 1 | Patty P_44767 | 1 | 1 | x | ||
| 5 | 1 | Isrel I_44767 | 0 | 1 | |||
| 6 | 0 | Dummy D_44767 | 1 | 1 | |||
| 7 | 1 | Patty P_447677 | 0 | 1 | |||
| 8 | 1 | Jarson X_44768 | 1 | 1 | x |
A - Cond1 - either 0 or 1
B - Person_code - first name, second name and number which represents a date
C - n/a - column not important for the case, included for the sake of numeration
D - Count - either 0 or 1 because it counts THE first occurence of B with formula:
COUNTIF($B$1:$B2;$B2)=1)+0 for row 2
COUNTIF($B$1:$B3;$B3)=1)+0 for row 3 and so on.
NOTE: The important thing is to count ONLY THE FIRST occurence (see rows 4 and 7 for an example).
E - Cond2 - either 0 or 1
F - n/a - column not important for the case, included for the sake of numeration
G - Result - IF (Cond1 + Count + Cond 2 = 3) THEN x
What the problem is
Currently Column D counts the first occurence of B. It does not take into account anything else. Just the first occurence in B column. However, I need it to ignore (i.e. do not count) rows where Cond1 + Cond2 is different than 2 (i.e. 0 or 1). Instead, it should look for a first occurence of B where Cond1 + Cond2 = 2 and count it.
So the table should look like this (pay attention to D3, D5 and G5):
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Cond1 | Person_code | n/a | Count | Cond2 | n/a | Result |
| __ | _______ | ________________ | _____ | ________ | _______ | _____ | ________ |
| 2 | 0 | Tom T_44767 | 1 | 1 | |||
| 3 | 0 | Isrel I_44767 | 0 | 1 | |||
| 4 | 1 | Patty P_44767 | 1 | 1 | x | ||
| 5 | 1 | Isrel I_44767 | 1 | 1 | x | ||
| 6 | 0 | Dummy D_44767 | 1 | 1 | |||
| 7 | 1 | Patty P_447677 | 0 | 1 | |||
| 8 | 1 | Jarson X_44768 | 1 | 1 | x |
Row 3 was ignored and the first occurence of 'Isrel I_44767' was found in row 5. Therefore an 'x' appeared in G in row 5.
I've tried to include additional conditions in D but can't get it to work. Any solution would be acceptable. It's okay to add additional columns, if needed or use a totally different approach.
I will be grateful for any advice on this.
