+ Reply to Thread
Results 1 to 5 of 5

problem with a sumproduct formula for a leaderboard sheet

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    belleville il
    MS-Off Ver
    Office 365/Excel 2013
    Posts
    18

    problem with a sumproduct formula for a leaderboard sheet

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList7&"'!$c$1:$c$50"),INDIRECT("'"&SheetList7&"'!$B$1:$b$50"),$I2,INDIRECT("'"&SheetList7&"'!$d$1"),$P$1))

    the problem part is the last criteria in the sumifs operation [INDIRECT("'"&SheetList7&"'!$d$1"),$P$1]

    for some reason when the reference for a specific venue is asked for it hiccups.

    the left most table is set and working for looking at every sheet and adding it up.

    the right table is the one im trying to get to work. L2 contains the formula in question for testing purposes. any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: problem with a sumproduct formula for a leaderboard sheet

    SUMIFS requires all ranges to be the same size so you can't include some 50 cell ranges and then 1 single cell range - you need to put the additional criterion outside SUMIFS but within the SUMPRODUCT like this

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList7&"'!$c$1:$c$50"),INDIRECT("'"&SheetList7&"'!$B$1:$b$50"),$I2)*(T(INDIRECT("'"&SheetList7&"'!$d$1"))=$O$1))
    Audere est facere

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,694

    Re: problem with a sumproduct formula for a leaderboard sheet

    Always nice to see the T() function making a useful appearance.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    7,005

    Re: problem with a sumproduct formula for a leaderboard sheet

    In L2 you can try. Your formula slightly modified. Insted of $P$1(Blank) , $O$1 is used and COUNTIF is used.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-18-2013
    Location
    belleville il
    MS-Off Ver
    Office 365/Excel 2013
    Posts
    18

    Re: problem with a sumproduct formula for a leaderboard sheet

    much obliged. daddylonglegs hit the nail right on the head for me

+ 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. Leaderboard Problem
    By Nick Roydhouse in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2012, 01:20 AM
  2. [SOLVED] sumproduct problem when more than 2 sheet in one book
    By alimamak in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2012, 09:11 AM
  3. Leaderboard on Multi Tabbed Sheet
    By Watson in forum Excel General
    Replies: 3
    Last Post: 09-22-2009, 11:35 AM
  4. Sumproduct formula problem
    By sclang in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-04-2008, 11:43 AM
  5. [SOLVED] Problem with a SUMPRODUCT Formula
    By carl in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-20-2006, 04:05 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