SumProduct formula only working on 1 of 3 sheets

[dmkjcl]

I have a excel doc that is driving me crazy and I need some help. The attached doc has 4 sheets on it. The first three hold data and the fourth is a viewer for it. I created a formula that searches for a name in the data tabs and carries the data over to the stats viewer. there is a drop menu at the top of the stats sheet that lets you choose which sheet's data you are viewing. The problem is that the formula works fine when you choose the third sheet, but when I choose another sheet I get errors. I'll attach the doc here. Any help would be great!

[shukla.ankur281190]

Try

E48=SUMIFS(INDIRECT("'"&$E$45&"'!I:I"),INDIRECT("'"&$E$45&"'!B:B"),$D48)
F48=SUMIFS(INDIRECT("'"&$E$45&"'!I:I"),INDIRECT("'"&$E$45&"'!B:B"),$G48)

And drag both down!!!

Please note that in Sumproduct function we must give range correctly. As per your sheets the ranges are not aligned correctly in each sheet, it will be better if you will use SUMIFS function which can easily handle large ranges.

Hope this will help you

[dmkjcl]

Wow thanks so much! Works great! Thanks

[shukla.ankur281190]

Thanks for feedback and Reputation !!!