# Toggling Sumproduct Criteria Off/On

Occasional Contributor

# Toggling Sumproduct Criteria Off/On

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.

3 Replies

# Re: Toggling Sumproduct Criteria Off/On

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))

# Re: Toggling Sumproduct Criteria Off/On

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?

# Re: Toggling Sumproduct Criteria Off/On

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:

=SUMPRODUCT(\$D\$1:\$D\$1000,IF(ApplyCap,\$E\$1:\$E\$1000,\$H\$1,\$H\$1000),--(\$C\$1:\$C\$1000=\$B1))

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.

=SUMPRODUCT(