# Using SUMPRODUCT() with two different criterias

#### VGPOP

##### Board Regular
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.

Last edited:

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Anyone?

Hi VGPOP,

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

Array enter with Ctrl-Shift-Enter
Code:
``````=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))))``````

Last edited:
Hi VGPOP,

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

Array enter with Ctrl-Shift-Enter
Code:
``````=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.

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.

Oh- I misunderstood that. Try...

Array enter with Ctrl-Shift-Enter
Code:
``````=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))))``````

Oh- I misunderstood that. Try...

Array enter with Ctrl-Shift-Enter
Code:
``````=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.

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.

Nicely done Teeroy.

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

Replies
5
Views
87
Replies
3
Views
76
Replies
3
Views
160
Replies
10
Views
271
Replies
13
Views
261

1,196,515
Messages
6,015,661
Members
441,914
Latest member
VBAllTheThings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back