+ Reply to Thread
Results 1 to 3 of 3

Summing With IF/INDEX/MATCH

  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Summing With IF/INDEX/MATCH

    Hi All,
    I have an INDEX & MATCH looking up values, and retrieving a "-" in case of an error or in case of a zero. I am looking to SUM all values in the "2021" column with the A407 criteria. Can't seem to get it working with adding the SUM before the INDEX in the formula.

    Please Login or Register  to view this content.
    [/CODE]

    Any help would be wonderful!

    Thanks,

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Summing With IF/INDEX/MATCH

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Summing With IF/INDEX/MATCH

    Why not use number formatting to display 0 as - rather than change numeric zeroes to "-" ?

    =IFERROR(INDEX('Inventory'!$1:$999999,MATCH($A407,'Inventory'!$A$1:$A$999999,0),MATCH("2021",'Inventory'!$1:$1,0)),0)

    with number format #,##0;-#,##0;"-";@.

    Anyway, summing. If you mean there could be several instances of the value of cell A407 in 'Inventory'!$A$1:$A$999999 (why not just use $A:$A?), and you want to sum all of the corresponding values in the 2021 column,

    =SUMIFS(INDEX('Inventory'!$1:$999999,0,MATCH("2021",'Inventory'!$1:$1,0)),'Inventory'!$A$1:$A$999999,$A407)

+ 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 with Summing?
    By Battletoads in forum Excel General
    Replies: 3
    Last Post: 09-04-2019, 03:04 PM
  2. Summing with several match index
    By Calios in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2018, 10:12 AM
  3. Summing value with 2 parameters - Index & match, or Vlookup?
    By Misterbashi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2015, 11:40 AM
  4. [SOLVED] Using Index, Match and summing the Multiple matches
    By shameus in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-14-2014, 05:16 PM
  5. summing an index(match,match) over multiple spreadsheets
    By andyjoewalnutt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-31-2013, 03:15 PM
  6. Replies: 1
    Last Post: 03-03-2009, 12:13 AM

Tags for this Thread

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