Summary Sheet Problem

[cwallace70]

I have an excel workbook that have 12 monthly tabs and on summary sheet. I need to get a summary for the following:
1. a summary of each agency (1R) in column C of each monthly tab
2. that have a line of business (UL & L99 & L121) in column D ofeach monthly tab
3. That have an amount next to each of theline of business in column E.
Is this possible with Sumproduct without making the formula take a very long time to calculate?
Would it be better to do this as another function such as pivot table or VBA? which I am not to familiar with setting up.

Thanks for your help

 

[cwallace70]

=SUMPRODUCT(INDIRECT("'"&SheetList&"'!C6:C50"),$A15,INDIRECT("'"&SheetList&"'!D6:D50"=$C$3),INDIRECT("'"&SheetList&"'!E6:E50"))

I am getting the #Value! error. I see the sheets listed in the drop down on the name box, but I cannot figure out why the formula is not giving me a result

 

[cwallace70]

Excel is telling me I have an error in my formula below and it highlights the red C3. Can someone look at this formula a tell me what I am doing wrong.


=SUMPRODUCT(SUMIF(INDIRECT($A$99:$L$99&"!C:C"),$A12,INDIRECT($A$99:$L$99&"!D:D"),C3,INDIRECT($A$99:$L$99&"!E:E")))

I am trying to get a sum on a summary sheet whithout using the "sheets" formula.