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.
CodePudding user response：
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 the first occurrence of B where Cond1 Cond2 = 2 and count it
=ARRAYFORMULA(IF(A2:A8 E2:E8=2, 1, 0))
now to account for occurrences/instances eg. not count duplicates (if that's what you are after - it's not clear from your question):
=ARRAYFORMULA(IF(1=COUNTIFS(
IF(A2:A10 E2:E10=2, B2:B10, ),
IF(A2:A10 E2:E10=2, B2:B10, ),
ROW(B2:B10), "<="&ROW(B2:B10)), 1, 0))
G - Result - IF (Cond1 Count Cond 2 = 3) THEN x
and G2 would be:
=INDEX(IF(A2:A10 D2:D10 E2:E10=3, "x", ))