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?