Combining Indirect and Sumproduct?

[erniepoe]

Hi all,

So on my Summary worksheet I'm trying to get a formula. The user chooses a month via a dropdown list, and that month is stored on the 'Controls' worksheet in cell D55. There are then 12 other tabs (one for each month) that I then want the formula to go to based on whatever month the user chooses. On that month tab, I then want to do a sumproduct formula where it looks for the name of the program (also from a dropdown menu, which is stored in the 'Controls' worksheet in cell B55), and then, if that program matches the list of programs in Column A from the relevant month tab (and also the program type from Column B), to sum the values in column C. I'm trying to do this so that anytime the user chooses a different month, the formula is always looking at the proper worksheet to do its calculations.

The part of the formula I've got for the above is as follows, but is not working correctly. Can anybody see where I've gone wrong?


Thanks for any help.


IF(E$19=2,INDIRECT(Controls!D55&SUMPRODUCT((A2:A400=Controls!B55)*(B2:B400="Admin")*(C2:C400))))

 

[Andrew Poulsom]

More like:

=IF(E$19=2,SUMPRODUCT((INDIRECT(Controls!D55&"!A2:A400")=Controls!B55)*(INDIRECT(Controls!D55&"!B2:B400")="Admin")*(INDIRECT(Controls!D55&"!C2:C400"))))

Note that the range references within INDIRECT won't adjust when you copy the formula because they are text.

 

[erniepoe]

that worked great! Thank you!