Search code examples
arraysexcelfunctionsumproduct

Sum Absolute Values Between Lower/Upper Limits

Lower limit is C2. Upper limit is D2. Need sum of absolute values of A2:A12 based on values in B2:B12 that are between lower/upper limits. Array formula below results in #N/A.
=SUMPRODUCT(IFS((B2:B12)>=C2,B2:B12)<=D2,ABS(A2:A12))

enter image description here

Solution

  • You almost had it. Try

    =SUMPRODUCT(--(B2:B12>=$C$2),--(B2:B12<=$D$2),ABS(A2:A12))

    With Sumproduct() you don't need an IF inside to test for a condition. If the condition evaluates to TRUE, then it will carry through the rest of the calculation.

    Use this formula and step through it with the Evaluate Formula tool to see how it operates.

    enter image description here