Sumproduct with multiple criteria and week range - Excel

Question

I m using SUMPRODUCT() function to get the score of each student under multiple criteria. In the below scenarion the criterias are:

1. Name: A
2. Week: 42 (falls in range 41 - 45)
3. Type: Exam

Using

=SUMPRODUCT((R2:R6=R8)*AND((S2:S6>=R9)*(T2:T6<=R9))*(U2:U6=R10);V2:V6)

i don't manage to get and success. Going through the formula i have understand that i do not manage to get the correct period.

Any ideas how to look on the correct time range?

Answer 1

Try using SUMPRODUCT() function in the following manner as shown in the screenshot, also MS365 users can use SUM() function here:

---

• Formula used in cell R11

=SUMPRODUCT((R8=R2:R6)*(R10=U2:U6)*(R9>=S2:S6)*(R9<=T2:T6)*V2:V6)

---

Edit: Also ensure to change the separators to the one that supports your regional settings, i.e. in place of commas it would be semi-colon ;. This is not needed as the formula multiplies the arrays and sum as well.

---

You don't need the AND() function in the formula as well, as * operator acts as the AND() function, and just for information the + would act as OR().