Toggling Sumproduct Criteria Off/On

Occasional Contributor

Let's say I have a worksheet that uses SumProduct across a relatively large array with a Sumproduct formula something like this:




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,




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.


3 Replies


If you have Microsoft 365 or Office 2021, you could use



Very good. I like it. I do have Microsoft 365, but I'm the only one in my group with it, and they won't get it for months, so I've tried to avoid Microsoft 365 features. [I'm the primary operator of the spreadsheet, but I'd rather that it not stop working if someone else needs to use it.] Hence, I'm wondering whether there's a similar function to "Sequence" in older versions of Excel?
Ah...nevermind...your solution works, and the use of Sequence is not necessary, as I don't mind adding a column of 100% values, so (modeled on your response) my solution looks something like this:


where the H column is simply a static column of 100% values.

And just in case anyone is interested, here's my actual final solution. I am generating the vectors dynamically, so I use the Offset/Indirect/Address combination to do that.

OFFSET(INDIRECT(ADDRESS(1,BF$16,,,"PrimaryWorksheet")),0,0,$C$21), 'Actual weights
OFFSET(INDIRECT(ADDRESS(1,BF$17,,,"PrimaryWorksheet")),0,0,$C$21) 'A column of 100% values

Thanks Hans.