Results 1 to 9 of 9

Thread: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Hi,

    I have formulas in worksheet 1 that pulls it's values in from worksheet 2. When instering a column into worksheet 2 my formulas change due to the insertion of the additional column so absolute referencing hasn't helped in my situation as I want to keep the formulas AS IS due to new data being added each day.

    My formula is as follows:

    SUMIF('worksheet2'!C3:L3,worksheet1'!C10,'worksheet2'!C18:L18)/2

    Can someone please show me how I would add INDIRECT to this formula. I have lots of other formulas but if someone can help with this formula I am thinking I should be able to update the rest. I've tried to find a solution but haven't had any luck, mainly because I am not understanding the INDIRECT function but it seems it's the only solution to my problem due to the insertion of new columns in my dataset

    Any help would be greatly appreciated.

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    1) If you insert a column in A,B or C then the formula will increment to D3:M3 (which makes sense)
    2) If you insert a column between D & L then the formula will increment to C3:M3
    3) If you insert a column after L then no change will be made to the formula.

    What would you want the formula to look at if 1) and 2) occurred?
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,091
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Hi, here is an option that is impervious to column insertions.

    Code:
    =SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2
    [code]your code[/code]

  4. #4
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Hi,

    My workbook has some VBA, the VBA inserts a new column with the new daily values into column C each day hence the range shift within my current formula
    My VBA also then deletes a column towards the end of the table which is column TT which doesn't show in this formula but does in others.

    In answer to your question:

    I will always need the formula to reference
    C3:L3.
    In C3:L3 I have the day's of the week, so if C10 equals let's say Friday then I want all Friday values from C18:L18 to be picked up

  5. #5
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Quote Originally Posted by FormR View Post
    Hi, here is an option that is impervious to column insertions.

    Code:
    =SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2


    Amazing, this works perfectly thank you so much!!

    How does it work, exactly? I'd like to try and change the rest of the formulas that also reference worksheet 2 but are slightly different to the current SUMIF, any additional help you can give is much appreciated

  6. #6
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Have worked out the 0,3 and 0,12 which was what I wasn't sure about and once I understood that I could see how the rest of the formula was working.

    Thanks again for your help! I spent almost all day trying to find way's to fix this insert column issue and you sorted this for me pretty much instantly.

  7. #7
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    [QUOTE=Ellie456;5303979]Amazing, this works perfectly thank you so much!!

    Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

    ='worksheet2'!F17

  8. #8
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Quote Originally Posted by FormR View Post
    Hi, here is an option that is impervious to column insertions.

    Code:
    =SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2

    Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

    ='worksheet2'!F17

  9. #9
    New Member
    Join Date
    Sep 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

    Quote Originally Posted by Ellie456 View Post
    Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

    ='worksheet2'!F17

    Have worked it out

    ='INDEX(worksheet2'!17:17,0,3)

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
  •