Forum Discussion
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)) Le...
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.