+ Reply to Thread
Results 1 to 7 of 7

Index, Match and Sumif Functions showing #Value Error

  1. 11-21-2021, 05:08 PM #1
    Jia26
    Jia26 is offline
    Registered User
    Join Date
    11-20-2021
    Location
    Islamabad,Pakistan
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Post Index, Match and Sumif Functions showing #Value Error

    Hi,
    I am having a problem with summing up of revenue for a period of time between date ranges of multiple outlets of a business. I am using two drop down lists to select the particular months between which the revenue should be summed up. I have used the Index, Match functions encapsulated in Sumifs function but I am getting a "#Value" error which I am not able to resolve.

    I am attaching a sample file containing the data and problem that I am incurring.

    Any kind of help will be highly appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-21-2021, 05:29 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: Index, Match and Sumif Functions showing #Value Error

    Use INDEX to specify the sum range, i.e., the column matching B15,etc.
    =SUMIFS(INDEX($B$2:$G$11,0,MATCH(B$15,$B$1:$G$1,0)),$A$2:$A$11,">="&B$13,$A$2:$A$11,"<="&D$13)

    0 returns all rows in the column.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 11-21-2021, 05:29 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Index, Match and Sumif Functions showing #Value Error

    B16:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    copied across.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 11-21-2021, 08:33 PM #4
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Index, Match and Sumif Functions showing #Value Error

    Better formula for the months: =EDATE(A2,1)
    Reply With QuoteRegister To Reply
  5. 11-22-2021, 02:03 PM #5
    Jia26
    Jia26 is offline
    Registered User
    Join Date
    11-20-2021
    Location
    Islamabad,Pakistan
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Index, Match and Sumif Functions showing #Value Error

    Thank you Ben. Your method works perfectly.
    Reply With QuoteRegister To Reply
  6. 11-22-2021, 02:05 PM #6
    Jia26
    Jia26 is offline
    Registered User
    Join Date
    11-20-2021
    Location
    Islamabad,Pakistan
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: Index, Match and Sumif Functions showing #Value Error

    Thank you Trevor, your method works perfectly!
    Reply With QuoteRegister To Reply
  7. 11-22-2021, 02:11 PM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Index, Match and Sumif Functions showing #Value Error

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] #ref error (Sumif Index Match)
    By gpmichels in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2021, 11:04 AM
  2. [SOLVED] SUMIF, index and match functions
    By Syed Saqib Ul Hassan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2019, 07:49 AM
  3. [SOLVED] Index match show all matches from list (only showing the first match...)
    By jakeh2475 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2018, 02:11 PM
  4. [SOLVED] Excel Function SUMIF with Look up functions or Index and Match
    By Sontu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2018, 08:47 PM
  5. Index and Match functions - #VALUE error
    By marlolao in forum Excel General
    Replies: 2
    Last Post: 10-28-2015, 03:41 AM
  6. [SOLVED] Intriguing error regarding index/match functions
    By AlanY in forum Excel General
    Replies: 2
    Last Post: 12-11-2014, 10:14 AM
  7. error using index and match functions in macro
    By skalaima in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-10-2013, 04:20 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1