# problem with a sumproduct formula for a leaderboard sheet

1. ## 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.

2. ## 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))

3. ## Re: problem with a sumproduct formula for a leaderboard sheet

Always nice to see the T() function making a useful appearance.

4. ## 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. ## Re: problem with a sumproduct formula for a leaderboard sheet

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

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

#### 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