+ Reply to Thread
Results 1 to 9 of 9

How to sum 11 adjacent cells to the one found with Index(match)?

  1. #1
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    How to sum 11 adjacent cells to the one found with Index(match)?

    I probably should have posted this in the formulas and functions section. How do I move it?

    I'm using an index(match) function to find a particular cell and that works. What I'd like to do is add that to the 11 cells to its right in order to get the yearly values.

    The only way I can think of doing it now is copying and pasting the formula for each value, manually changing the index array over 1 each time and adding them all together

    For example:
    =IFERROR(INDEX('CF'!$D$360:$D$708,MATCH(Summary!O3,'CF'!$C$359:$C$708,0)+12,1),0) + =IFERROR(INDEX('CF'!$E$360:$E$708,MATCH(Summary!O3,'CF'!$C$359:$C$708,0)+12,1),0)

    Then doing this 10 more times.

    There's got to be a better way. Is there a basic formula to do it?
    Last edited by max3732; 11-23-2020 at 01:18 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    Try this:

    =IFERROR(SUM(INDEX('CF'!$D$360:$D$708,MATCH(Summary!O3,'CF'!$C$360:$C$708,0)):INDEX('CF'!$O$360:$O$708,MATCH(Summary!O3,'CF'!$C$360:$C$708,0))),0)

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    Quote Originally Posted by Pete_UK View Post
    Try this:

    =IFERROR(SUM(INDEX('CF'!$D$360:$D$708,MATCH(Summary!O3,'CF'!$C$360:$C$708,0)):INDEX('CF'!$O$360:$O$708,MATCH(Summary!O3,'CF'!$C$360:$C$708,0))),0)

    Hope this helps.

    Pete

    Couldn't get it to work.

    The "12" was missing from what you posted, but I added it in to make it like this:

    =IFERROR(SUM(INDEX('CF'!$D$360:$D$708,MATCH(Summary!O3,'CF'!$C$360:$C$708,0)+12):INDEX('CF'!$O$360:$O$708,MATCH(Summary!O3,'CF'!$C$360:$C$708,0)+12)),0)

    Something else I was thinking. Is there something like "sumif", but that would work with different ranges? For example, could I start from scratch and tell excel to return the sum of a range where it meets a certain criteria?

    For example, lets say you have a column of names like this:

    Apple
    Pear
    Banana

    If in the columns the columns to the right there are values could you highlight all of them and then if you're in another sheet could you tell excel to only sum the ones next to "Pear" for example?
    Last edited by max3732; 11-23-2020 at 05:57 PM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    "...What I'd like to do is add that to the 11 cells to [the] right in order to get the yearly values. ..."
    However:
    INDEX('CF'!$D$360:$D$708,MATCH(Summary!O3,'CF'!$C$359:$C$708,0)+12,

    returns a row and adds 12 to the row; the column remains D.

    Try:
    Please Login or Register  to view this content.
    Ben Van Johnson

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    In my formula the INDEX terms return a cell reference, rather than a value. Suppose your MATCH term finds a match in row 400 of column C, then the formula boils down to:

    SUM(D400:O400)

    so you don't need the +12.

    It would help if you attached a sample Excel workbook - the yellow banner at the top of the screen tells you how to do this.

    Hope this helps.

    Pete

  6. #6
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    Quote Originally Posted by Pete_UK View Post
    In my formula the INDEX terms return a cell reference, rather than a value. Suppose your MATCH term finds a match in row 400 of column C, then the formula boils down to:

    SUM(D400:O400)

    so you don't need the +12.

    It would help if you attached a sample Excel workbook - the yellow banner at the top of the screen tells you how to do this.

    Hope this helps.

    Pete
    That was my mistake. I got it to work now. Not sure what I was thinking. Thank you so much for your help!

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  8. #8
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    Quote Originally Posted by Pete_UK View Post
    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete
    I will definitely mark it as solved and thank everyone for helping. Just one more question. Can you explain how it works? I'd like to know so I understand what I'm doing vs. just copying and pasting.

    I know you said it boils down to taking the sum of those values after finding a match, but how?

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: How to sum 11 adjacent cells to the one found with Index(match)?

    The INDEX function can be used to in two ways. The first way, and probably the most common, is to return a value from some range, i.e.:

    INDEX(range, row, column)

    where the row and column terms identify the particular cell where you want to get the data from (often from a MATCH function). When we have a 1-D range, only one of the row/column parameters needs to be included.

    However, it can also be used to return a cell reference, which is how I am using it here. It avoids using the INDIRECT function, which is volatile.

    Both MATCH terms are identical, and are trying to find where the value in the cell Summary!O3 can be found in the range $C$360:$C$708 of the CF sheet - this will return the (relative) row number within that range. The first INDEX term then picks out that cell within the range $D$360:$D$708 of the CF sheet - note that the ranges are the same size (from row 360 to row 708) so the relative position is the same in both -, whereas the second INDEX term returns the cell from the range $O$360:$O$708. Note the : in the formula - this would basically result in:

    SUM($D$400:$O$400)

    if the matched cell occurred on row 400, so it would sum the values in those 12 cells on row 400. The IFERROR term intercepts any errors which might occur (e.g. if no exact match is found).

    Hope this helps.

    Pete
    Last edited by Pete_UK; 11-24-2020 at 01:19 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX/MATCH generates incorrect result by referencng nearest cell if match not found
    By aglawrence in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2017, 08:59 AM
  2. [SOLVED] Comparing adjacent cell if match is found
    By brannan2000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2016, 09:10 PM
  3. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  4. [SOLVED] Match one cell with another, if match found copy adjacent cells
    By Xiophoid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-07-2013, 05:50 AM
  5. [SOLVED] VBA Code - Compare Adjacent Cells In two columns and clear contents when a match is found
    By Langer101 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-03-2013, 06:22 AM
  6. [SOLVED] Comparing Two Columns and Copy Adjacent Column IF Match Found
    By 10AVATAR in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-29-2012, 04:33 PM
  7. [SOLVED] Index and Match for first 3 items found, including cells with same values
    By hazelwouldbe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2012, 09:17 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1