Dec 14 2022 01:34 PM
Let's say I have a worksheet that uses SumProduct across a relatively large array with a Sumproduct formula something like this:
=Sumproduct($D$1:$D$1000,$E$1:$E$1000,--($C$1:$C$1000=$B1))
Let's say that the values in column E are weighting factors, and I'd like to see the results with or without them, so I want a central switch, through which I can turn that condition on or off. I could do something like the following, but I'm looking for a more elegant solution.
=If (ApplyCap = True,
Sumproduct($D$1:$D$1000,$E$1:$E$1000,--($C$1:$C$1000=$B1)),
Sumproduct($D$1:$D$1000,--($C$1:$C$1000=$B1)))
where ApplyCap is a single named cell set to either True or False.
Thoughts on how I could do this either more simply or with better preformance. Note that my actual formula is quite a bit more complicated, so I'd rather not replicate it as in the above attempt.
Dec 14 2022 01:49 PM
If you have Microsoft 365 or Office 2021, you could use
=SUMPRODUCT($D$1:$D$1000,IF(ApplyCap,$E$1:$E$1000,SEQUENCE(1000,,,0)),--($C$1:$C$1000=$B1))
Dec 14 2022 06:37 PM
Dec 14 2022 07:05 PM