Sumproduct with excel add-in formula

[burlywood66]

I'm using a proprietary add-in that has its own formulas. The formula is essentially a vlookup to a value in a third party software. I'm trying to modify the formula to return multiple values using a sumproduct, but it is not working - I am only get the value of the first item.

=SUMPRODUCT(cw_map("BR","OPER1->"&{"113-000","114-000"}&""))

I'm trying to return the value of 113-000 and 114-000. Am I using the curly brackets with sumproduct correctly?

[Speshul]

As far as I can tell, your SUMPRODUCT going to try and add "BR133-000" to "OPER1->114-000". You only have one array listed in SUMPRODUCT

An example of a working sumproduct with {}'s would be

=SUMPRODUCT({1,4,4},{3,8,2})

This will multiply 1x3, 4x8, 4x2, and SUM the results to 43 (3 + 32 + 8 = 43)

What is the syntax of cw_map()?

[burlywood66]

cw_map pulls from an accounting software based on 3 levels of account detail. BR is current year, OPER1 is the account type, and 113-000 is the account - it's just a structure for identifying where/which account balance to pull

i'm trying to use 1 cw_map formula to pull 2 or more balances.

[Speshul]

So the goal would be something like
=SUM(cw_map("BR","OPER1->","113-000"),cw_map("BR","OPER1->","114-000"))

Are there too many account numbers to be summed together to make that method viable? Why are you looking to use SUMPRODUCT?

[burlywood66]

My spreadsheet is setup that column A has a single value associated with multiple accounts. Column B has the lookup/sum formula. Another sheet contains the associated accounts for the values from column A above. Because I use multiple sheets with this A/B setup, I want to avoid any hard-coding. My sum formula currently uses a VLOOKUP with a sumproduct, references columns 2,3,4,etc (where 2,3,4 contain the account numbers).

If this is hard to visualize, I can try to send you something.

Thanks for helping out.