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:

 

=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

@EnglandPrevails 

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

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:

=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(
OFFSET(INDIRECT(ADDRESS(1,BF$12,,,"PrimaryWorksheet")),0,0,$C$21),
IF(BF$18="IncludeCap",
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
),
--(OFFSET(INDIRECT(ADDRESS(1,BF$15,,,"PrimaryWorksheet")),0,0,$C$21)=$F29))

Thanks Hans.