SUMIFS/SUMPRODUCT for a list of criteria

[jrashton92]

Hey guys,

Been trying for an hour or so to figure this out with no luck. I'm guessing it will be a combo sumifs/sumproduct but I always struggle with sumproduct. Could anybody lend a hand? Thoroughly stumped.

test.PNG

[XLent]

I think your expected results are incorrect as 2021 is not valid, per your lease agreement > 0 requirement

=SUMPRODUCT(SIGN(SUMIF(E8:E20,A8:A26,F8:F20)),B8:B26)

=A2/SUMPRODUCT(SIGN(SUMIF(E2:E20,A8:A26,F2:F20)))

[Bo_Ry]

Please try at
A2
=SUMPRODUCT(SUMIFS(B8:B26,A8:A26,E8:E20/(F8:F20>0)))
A3
=A2/SUMPRODUCT(COUNTIF(A8:A26,E8:E20/(F8:F20>0)))

Unit 2021 shouldn't be include.

[jrashton92]

That's awesome, thanks! Never used the sign function before

[jrashton92]

Perfect, thanks!