Using SUMPRODUCT() with two different criterias

[VGPOP]

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.

 

[VGPOP]

Anyone?

 

[Jerry Sullivan]

Hi VGPOP,

There might be a better way, but I believe this works...

Array enter with Ctrl-Shift-Enter

=SUM(IF(ROW($D$3:$D$100)>=LARGE(IF($D$3:$D$100="P",IF($F$3:$F$100=1,ROW($D$3:$D$100))),
     MIN(5,COUNTIF($D$3:$D$100,"P"))),
     IF($D$3:$D$100="P",IF($F$3:$F$100=1,$E$3:$E$100))))

 

[VGPOP]

Hi VGPOP,

There might be a better way, but I believe this works...

Array enter with Ctrl-Shift-Enter

=SUM(IF(ROW($D$3:$D$100)>=LARGE(IF($D$3:$D$100="P",IF($F$3:$F$100=1,ROW($D$3:$D$100))),
     MIN(5,COUNTIF($D$3:$D$100,"P"))),
     IF($D$3:$D$100="P",IF($F$3:$F$100=1,$E$3:$E$100))))

Thank you Jerry! Most appreciation! It does work.

 

[VGPOP]

The above formula does work. But it works only when it finds the last 5 passed scores that have a "1" score.

I was wondering to have last 5 passed scores, regardless, and only sum if a "1" score was determined.

In the above example:

114, 1
66, 0
102, 1
88, 0
78, 0

Only 114 + 102 should be added for a result of 216.

 

[Jerry Sullivan]

Oh- I misunderstood that. Try...

Array enter with Ctrl-Shift-Enter

=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",IF($F$3:$F$100=1,$E$3:$E$100))))

 

[VGPOP]

Oh- I misunderstood that. Try...

Array enter with Ctrl-Shift-Enter

=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",IF($F$3:$F$100=1,$E$3:$E$100))))

It works!. Thanks a million.

I am actually using both formulas for my data. This is great. Thanks a lot.

 

[Teeroy]

While I think Jerry's way is probably neater I had just about finshed an alternate way with sumproduct so I'm posting anyway :

=SUMPRODUCT((((D$3:D$100="P")*ROW(D$3:D$100))>=LARGE((D$3:D$100="P")*ROW(D$3:D$100),MIN(5,COUNTIF($D$3:$D$100,"P"))))*1,(F$3:F$100),(E$3:E$100))

Note: CSE not required.

 

[Jerry Sullivan]

Nicely done Teeroy.

 

[VGPOP]

Thanks Teeroy. Nice to know you can use it this way as well.