excelexcel-formularangesumproduct

Sumproduct with multiple criteria and week range - Excel

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?

enter image description here

Solution

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

    enter image description 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.

    enter image description here

    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().