Explain SUMPRODUCT formula function

[gshaffner]

The table below is an analysis of tasks and the variances between actuals and standard. My original intent was to rank equally column G variances. As they are both negative and positive I cannot just use the RANK function in Excel. I found a formula using SUMPRODUCT, which worked, but I do not completely understand how it works. A coworker modified the original formula in column I and the result is in column J. How is it that both fornulas work? Can someone breakdown what the SUMPRODUCT formula in column I is actually doing? I don't want to use a formula that I do not understand how it works.

Many thanks for any help.

Formula in column I:
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$17))*($G$3:$G$17<>""))-SUMPRODUCT((ABS(G3)=ABS($G$3:$G$17))*($G$3:$G$17<>""))+1

Formula in column J:
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$17))*($G$3:$G$17<>""))

1 A B C D E F G H I J 2 Actuals Inspect Move Function Standard Variance Variance % Ranking 1 Ranking 2 3 Task 1 18.4 5.0 10.0 5.4 20.4 2.0 -9.8% 11.0 11 4 Task 2 26.2 17.7 2.7 40.0 60.4 34.2 -56.6% 6.0 6 5 Task 3 32.1 7.5 23.9 31.4 (0.7) 2.3% 15.0 15 6 Task 4 35.8 5.0 32.6 37.6 1.8 -4.9% 14.0 14 7 Task 5 17.6 5.0 7.5 12.5 (5.1) 40.5% 9.0 9 8 Task 6 63.2 1.4 8.3 94.9 104.6 41.4 -39.6% 4.0 4 9 Task 7 117.2 6.8 8.7 167.3 182.8 65.6 -35.9% 2.0 2 10 Task 8 34.2 - (34.2) 0.0% 5.0 5 11 Task 9 36.1 0.5 3.8 27.5 31.8 (4.3) 13.5% 10.0 10 12 Task 10 35.1 3.5 3.3 26.4 33.2 (1.9) 5.8% 12.0 12 13 Task 11 91.3 14.0 2.2 9.0 25.2 (66.1) 262.3% 1.0 1 14 Task 12 73.1 6.5 4.1 60.6 71.2 (1.9) 2.7% 13.0 13 15 Task 13 40.1 3.1 20.3 11.1 34.5 (5.6) 16.2% 8.0 8 16 Task 14 87.7 19.4 3.5 77.0 99.9 12.2 -12.2% 7.0 7 17 Task 15 88.9 18.0 126.9 144.9 56.0 -38.7% 3.0 3 18 SUBTOTAL 797.0 77.9 102.4 710.1 890.4 93.4 -10.5% 19 Task 16 67.5 23.0 23.0 (44.5) 193.6% 20 Task 17 98.0 63.2 63.2 (34.8) 55.0% 21 Task 18 - 63.1 63.1 63.1 -100.0% 22 Subtotal 165.5 126.3 23.0 - 149.3 (16.2) 10.8% 23 TOTAL 962.5 204.2 125.4 710.1 1,039.7 77.2 -7.4% <colgroup><col style="mso-width-source:userset;mso-width-alt:768;width:16pt" width="21"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:2048; width:42pt" width="56" span="3"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" width="70" span="2"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Jonmo1]

You basically have 3 copies of the same sumproduct function there.
The only difference is 2 of them use
ABS(G3)<=ABS($G$3:$G$17)
And one uses
ABS(G3)=ABS($G$3:$G$17)

The one in I looks complicated, but it's really just
Sumproduct1 minus Sumproduct2 + 1

So I'm really only going to address a single sumproduct function
And I'm going to change it to just 3 rows instead of 15, just for sake of ease.
So lets take this
=SUMPRODUCT((ABS(G3)<=ABS($G$3:$G$5))*($G$3:$G$5<>""))

ABS removes the sign from a number, so a negative becomes a positive..
G3 = 2, so ABS(G3) = 2 (if it was G5, -0.7 would become 0.7)
=SUMPRODUCT((2<=ABS($G$3:$G$17))*($G$3:$G$17<>""))
G4 and G5 = 34.2 and -0.7
=SUMPRODUCT((2<=ABS({2,34.2,-0.7}))*({2,34.2,-0.7}<>""))
ABS converts negatives to positives
=SUMPRODUCT((2<={2,34.2,0.7})*({2,34.2,-0.7}<>""))

Now you have 2 arrays of criteria expressions with True or False results.
is 2 <= {2, 34.2, 0.7}
and
is{2, 34.2, 0.7} Not Blank

=SUMPRODUCT({TRUE,FALSE,TRUE}*{TRUE,TRUE,TRUE})

Now those 2 arrays are multiplied against each other.
When applying math operations (like * - + or /), then TRUE = 1 and FALSE = 0
=SUMPRODUCT({1,0,1}*{1,1,1})
1*1 = 1
0*1 = 0
1*1 = 1
=SUMPRODUCT({1,0,1})
The array is then summed,
1+0+1 = 2


Hope that helps.

 

[gshaffner]

That is perfect. I understand how the feature works. Thank you. Happy Holidays!

 

[Aladin Akyurek]

I think

=SUMPRODUCT({TRUE,FALSE,TRUE}*{TRUE,TRUE,TRUE})

evaluates to

SUMPRODUCT({1,0,1})

without first getting TRUE to 1, etc. then multiplying pairwise.