User Profile
EnglandPrevails
Copper Contributor
Joined Dec 06, 2022
User Widgets
Recent Discussions
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( 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.1.2KViews0likes0CommentsRe: 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?1.2KViews0likes1CommentToggling 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.1.3KViews0likes3CommentsRe: Vector Cross Reference - Vector Lookup
HansVogelaar I don't want to overcomplicate my question, so I'll try to answer your question in terms of simplified analogous structure. Let's say that my primary table has 1000 rows and shows sales activity. Each sales activity (one row) is associated with a sales person, and each sales person is associated with a category of sales person. The sales activity is measured weekly in $ of sales (many columns across the row). There's a secondary reference table with 100rows that lists all the sales people and indicates their commission % by week. What I'm attempting to do is to calculate for a given week the commission $, at the sales person category level. I use SumProduct to sum across all rows relevant to that category of sales person, and then before aggregating I'm trying to multiply each individual sales person's sales activity by the corresponding commission % which is stored at the sales person level (not the category level). Something like the following. [Note that your question is prompting me to clean my original formula up a bit, especially now using my more detailed example.] =Sumproduct ( ((Sales Activity in $ for a particular week) * (Commission % for that Sales Person during that week)) * (Filter by Sales Person Category) ) =Sumproduct ( (A1:A1000),([lookup](B1:B1000)),--(B1:B1000=Sales Clerk) ) Where "[lookup]" is a placeholder for a vector lookup of all Sales people in the primary table to find all their commission percentages for that week in the secondary table. My question was whether a vector cross reference lookup of this type can be performed within a vector formula.867Views0likes0CommentsVector Cross Reference - Vector Lookup
I'm trying to adjust an existing vector formula that is working, to include an additional factor. The factor is based on a lookup of a parameter in the primary table. I've tried to find a way to do a lookup using a vector of values each of which should result in a single response. Conceptually, it should be something like this: =Sumproduct (A1:A1000,--(B1:B1000="JohnDoe"),[lookup](B1:B1000)) A and B are columns in the primary table, and the lookup is in a reference data table. I typically prefer to use Index/Match for my cross reference lookups, but I don't see how to use that here, since it typically returns a single value. How can I execute a vector of lookups and return to the function a vector of resulting cross reference values? I've used the notation "[lookup]" above simply to indicate that I don't know the structure for this kind of cross reference lookup. Thanks990Views0likes2Comments
Recent Blog Articles
No content to show