problem with a sumproduct formula for a leaderboard sheet

[indiana115]

=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.

[daddylonglegs]

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

[XOR LX]

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

[kvsrinivasamurthy]

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.

[indiana115]

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