• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic name in sum product

B

bluetaurean

Member
I have two tables

a. Forecfast table b. Profit table


Product Jan Feb Mar

FORECAST 1111 25 25 25

FORECAST 2222 10 20 20

FORECAST 3333 20 20 20


PROFIT 1111 2 2 2

PROFIT 2222 3 3 3

PROFIT 3333 5 5 5


I have macro to create names based on product like - forecast1111, forecast2222, profit1111, profit2222 etc.


I want to use these name in a sumproduct formula but it does not work


Profit formula for product listed in Cell B10 = sumproduct ("forecast"&B10,"profit"&B10) which should evaluate to sumproduct(forecast1111,profit1111). However the formula returns a error
 
What error are you getting?


Are you naming the entire range of the data? or just the headers? If you are naming the entire range it should work.


Can you post an example workbook?
 
Are "forecast" and "profit" Column Arrays like A:A or are they ranges with rows? I am pretty sure I had this problem and spent ages on it and didn't realise you can't use sumproduct and column arrays like A:A so I had to use A1:A1000 range definitions in the formulas instead to capture my "dynamic" range that I would never grow out of. I have no time to check this out further at the moment so I hope my memory serves me here.
 
Hi,


I get a #value error. I have put below an example of ranges etc that are defined.


Type Product Jan Feb Mar

FORECAST 1111 25 25 25

FORECAST 2222 10 20 20

FORECAST 3333 20 20 20


PROFIT 1111 2 2 2

PROFIT 2222 3 3 3

PROFIT 3333 5 5 5


Product sum product

1111 150 This works

1111 #VALUE! This does not.


Ranges defined

FCST1111 = $C$2:$E$2

PROFIT1111=$C$6:$E$6

ETC
 
Ulrik, this gives a #ref error. Please note the numbers "1111" are not cell references but product names, they could have been "apple","mango". Efficitenly my range name could be FCSTApple, FCSTMango, ProfitApple, ProfitMango, etc.


Therefore when the user selects to view the total profit for apple, the formula must be able to do sumproduct(FCSTApple, ProfitApple). Somehow the sum product formula is not accepting a concatenation of the words FCST & Apple to give a range name "FCSTAPPLE" which has the numbers


thanks for any help you can provide
 
Ulrik's comment should work fine. Make sure you've got the Indirect function in both parts of the sumproduct and that the quotes and & are correct. Cut and paste the function onto the Forum if it still doesn't work.
 
TYPE PRODUCT Period1 Period2 Period3

FCST APPLE 10 15 20

FCST ORANGE 20 15 10

FCST MANGO 15 15 15


Profit APPLE 2 2.25 1.75

Profit ORANGE 3 1 3

Profit MANGO 4 4 4


Result Area

Total Profit Apple(B13)=SUMPRODUCT("FCST"&B13,"PROFIT"&B13) result = #VALUE!

Apple(B14)=SUMPRODUCT(FCSTAPPLE,PROFITAPPLE) result = 90

Apple(B15)=SUMPRODUCT("fcst"&INDIRECT(B15),"profit"&INDIRECT(B15)) Result = #REF!

As you will see only the second formula works, but it is not suitable as the range has to be hardcoded into the formula where i need the product to be user defined - "FCST"&Apple/orange/mango.


The actual data tables i have has over 500 products.
 
Let's look at what we need to have happen here. You want a variable range reference to work inside the Sumproduct. Indirect does that, but you're now dealing with text strings that fit together to make the cell reference. Here's the first statement:


Apple(B13)=SUMPRODUCT(Indirect("FCST"&B13),indirect("PROFIT"&B13))


Note that there are now two indirect statements inside the sumproduct. Whenever I use Indirect, I test the text strings to make sure they are the names of my defined ranges. Some of these strings can be really annoying to put together correctly.


Let's look at the next one:


Apple(B15)=SUMPRODUCT("fcst"&INDIRECT(B15),"profit"&INDIRECT(B15))


You've got an indirect statement in each sumproduct field. But you're trying to combine a text string with part of a cell reference and Sumproduct doesn't know what to do with that. I changed this just a bit to give:


Apple(B15)=SUMPRODUCT(INDIRECT("fcst"&B15),INDIRECT("profit"&B15))


Now Indirect is getting a text string and yielding a cell reference to Sumproduct. Should work.


You may want to think about defining 1000 (500 * 2) named ranges. This might be a good place for an MMULT function and 2D matricies. Hard to say without knowing what you ultimately want to accomplish here.
 
You must log in or register to reply here.
Back
Top