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

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

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: