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

 

[texasalynn]

take a look at this post

 

[cwallace70]

I referred to the previous thread, where are the sheets names referenced in my spreadsheet?

 

[texasalynn]

where SheetList refers to a range that lists the relevant sheet names

you will need to create a list of sheet names

 

[cwallace70]

Is this done in Insert > Name > Define ?
If so. I added the names of the monthly tabs

 

[texasalynn]

1) on an empty sheet type in the names of your worksheets
2) select the cells you just created
3) select the named box (found next to the formula bar) and type "SheetList"

 

[cwallace70]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2:C50"),A10,INDIRECT("'"&SheetList&"'!D2:D50")))

Above isthe formula I am using and I am getting a #NAME? error message.

C2:C50 is the agency I need to calculate the data for (1R) and D2:D50 is the line of business (UL), then I need to add another piece to this formula to pull the dollar amounts in column E.
And If I wanted to include morethan one line of business in a fromula how do I go about doing that. For example if I wanted a sum for UL and L99 in the same formula?

Thanks

 

[cwallace70]

1) on an empty sheet type in the names of your worksheets
2) select the cells you just created
3) select the named box (found next to the formula bar) and type "SheetList"

I followed the steps above and All I am getting is zeros. I assume this is because I have not referenced a cell with the actual dollar amount to sum up

 

[texasalynn]

#NAME tells me it did not find the "SheetList". If you click on the drop-down of the named range do you see it?

 

[cwallace70]

Yes I do see the sheet list names, and I no longer have the #name? error.
I am just getting zeros as the results.

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2:C50"),A10,INDIRECT("'"&SheetList&"'!D2:D50")))

Above isthe formula I am using and I am getting all zeros.
C2:C50 is the agency I need to calculate the data for (1R) and D2:D50 is the line of business (UL), then I need to add another piece to this formula to pull the dollar amounts from column E.
And If I wanted to include more than one line of business in a fromula how do I go about doing that. For example if I wanted a sum for UL and L99 in the same formula?