Results 1 to 7 of 7

Thread: Changing columns in Sumproduct with cell references

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Changing columns in Sumproduct with cell references

    Hi Guys, sorry if this has been covered before, I searched a lot but couldnt find an answer.

    In my formula, I need to change columns with cell references. My formula is:

    =SUMPRODUCT(--($C$3:$C$57=$BG3),BD$3:BD$57,H$3:H$57)

    I have weights in columns BD, BE and BF. In the above formula, I need to be able to change the weights to column BD, BE and BF.

    I thought that if I have a row at the top of the column, I can then manually enter the text BD, BE or BF and have a formula that changes the weights based on what I entered.

    I cant seem to figure this out. Thanks.

  2. #2
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing columns in Sumproduct with cell references

    please guys my boss is gonna fire me, i have little kids to feed

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,042
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Changing columns in Sumproduct with cell references

    Welcome to Mr Excel forum

    Maybe this

    Put, say, in A2 the column of interest: BD,BE or BF

    Try this formula
    =SUMPRODUCT(--($C$3:$C$57=$BG3),INDEX(BD$3:BF$57,0,MATCH(A$2,{"BD";"BE";"BF"},0)),H$3:H$57)

    I hope kids do not get hungry

    M.
    Last edited by Marcelo Branco; Nov 22nd, 2017 at 11:53 AM.

  4. #4
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing columns in Sumproduct with cell references

    My kids thank you, Sir!

    I figured out an easier way (its a wonder what hunger does to a man's motivation). I decided to use named ranges with INDIRECT function. Worked like a charm. Off to Chinese buffet now

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,042
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Changing columns in Sumproduct with cell references

    You are welcome.

    By the way, avoid the use of volatile functions like INDIRECT. Take a look at:
    http://www.decisionmodels.com/calcsecretsi.htm

    M.

  6. #6
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing columns in Sumproduct with cell references

    Learned something new today, thanks. In my worksheet there are multiple columns in which I need to indicate the weights. So in your example, it wont be just A2 with the weight name but A2, B2 and C2. Dont mean to impose but if you get a moment please update your formula for my case. Thanks.

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,042
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Changing columns in Sumproduct with cell references

    Quote Originally Posted by dazednconfuzed View Post
    Learned something new today, thanks. In my worksheet there are multiple columns in which I need to indicate the weights. So in your example, it wont be just A2 with the weight name but A2, B2 and C2. Dont mean to impose but if you get a moment please update your formula for my case. Thanks.
    Not sure i understand what you are trying to do, but i think all you have to do is to adjust on each formula: the cell reference, the ranges and the array in the INDEX/MATCH part (in blue)
    =SUMPRODUCT(--($C$3:$C$57=$BG3),INDEX(BD$3:BF$57,0,MATCH(A$2,{"BD";"BE";"BF"},0)),H$3:H$57)

    To understand what this part does
    INDEX(range,0, column_num)
    the 0 (second argument: row_num) forces the formula to provide an array composed of all rows (in the case, rows 3:57)
    MATCH gets the relative position, of the value entered in A2, in the array {"BD";"BE";"BF"), that is: if A2 = BD, match returns 1; if BE, returns 2; if BF returns 3

    So with A2, for example, equal BE the INDEX/MATCH returns all rows of the second column of the range BD3:BF57 that is exactly BE3:BE57

    Hope i made myself clear

    M.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •