Hello,
I am keeping track of three columns and I want to be able to calculate the last 5 entries that meet two criteria, for example:
D3: P, E3: 106, F3: 1
D4: F, E4: 106, F4: 1
D5: P, E5: 78, F5: 0
D6: P, E6: 88, F6: 0
F 96 0
F 97 1
P 102 1
P 66 0
F 107 0
F 102 1
P 114 1
D14: F, E14: 96, F14: 1
...
...
...
D100: (blank), E100: (blank), F100: (blank)
I want to be able to sum the last 5 entries of passed (P) and also scored a "1".
In the above example, the last passed entries were:
114, 1
66, 0
102, 1
88, 0
78, 0
So, I am looking at 216 (114 + 102) ("passed" and "1" scores).
Below is the formula I am using to calculate the last five passed scores.
SUM(IF(ROW($D$3:$D$100)>=LARGE(IF($D$3:$D$100="P",ROW($D$3:$D$100)),MIN(5,COUNTIF($D$3:$D$100,"P"))),IF($D$3:$D$100="P",E3:E100))), "")
How can I combined the last five passed scores that also scored a 1 in those scores? (Two criteria)
Sumproduct?
Any help, I would appreciate it thanks.
Sorry for long explanation.
I am keeping track of three columns and I want to be able to calculate the last 5 entries that meet two criteria, for example:
D3: P, E3: 106, F3: 1
D4: F, E4: 106, F4: 1
D5: P, E5: 78, F5: 0
D6: P, E6: 88, F6: 0
F 96 0
F 97 1
P 102 1
P 66 0
F 107 0
F 102 1
P 114 1
D14: F, E14: 96, F14: 1
...
...
...
D100: (blank), E100: (blank), F100: (blank)
I want to be able to sum the last 5 entries of passed (P) and also scored a "1".
In the above example, the last passed entries were:
114, 1
66, 0
102, 1
88, 0
78, 0
So, I am looking at 216 (114 + 102) ("passed" and "1" scores).
Below is the formula I am using to calculate the last five passed scores.
SUM(IF(ROW($D$3:$D$100)>=LARGE(IF($D$3:$D$100="P",ROW($D$3:$D$100)),MIN(5,COUNTIF($D$3:$D$100,"P"))),IF($D$3:$D$100="P",E3:E100))), "")
How can I combined the last five passed scores that also scored a 1 in those scores? (Two criteria)
Sumproduct?
Any help, I would appreciate it thanks.
Sorry for long explanation.
Last edited: