SUMPRODUCT with multiple tabs as a condition

[Ryckiej]

I have a large spreadsheet that contains sales data for multiple lines of business and multiple years of sales and expense. We have business in 8 separate states and each state has its own tab. I have a spreadsheet that uses sumproduct to give a 5 year summary for a specified line of business over a specified 5 year range. But to make it work for a specified state, I have to use an if-then statement and then duplicate the formula for the loss ratio for every state. Is there a way to set a criteria that will address a specified tab? Here's the formula that I'm currently using. =IF($B$3="State 1",SUMPRODUCT('State 1'!$C$2:$L$13*('State 1'!$A$2:$A$13='Loss Ratio'!$B$1)*('State 1'!$B$2:$B$13='Loss Ratio'!$B9)*('State 1'!$C$1:$L$1='Loss Ratio'!C$8)),IF($B$3="State 2",SUMPRODUCT('State 2'!$C$2:$L$13*('State 2'!$A$2:$A$13='Loss Ratio'!$B$1)*('State 2'!$B$2:$B$13='Loss Ratio'!$B9)*('State 2'!$C$1:$L$1='Loss Ratio'!C$8)),IF($B$3="State 3",SUMPRODUCT('State 3'!$C$2:$L$13*('State 3'!$A$2:$A$13='Loss Ratio'!$B$1)*('State 3'!$B$2:$B$13='Loss Ratio'!$B9)*('State 3'!$C$1:$L$1='Loss Ratio'!C$8)),"Incorrect State")))

 

[saboh12617]

Hello,

You could use INDIRECT, or if you're on office 365 maybe an arrayformula in which you stack all your sheets values + names in on big array, and then filter this array based on the sheet name.

 

[Ryckiej]

I'm not very familiar with the INDIRECT formula. Could you give me an example? Also, will this work if the state tabs are all contained within a separate spreadsheet?

 

[saboh12617]

So based on the example you provided, i think the formula below should be equivalent

=SUMPRODUCT(
INDIRECT("'" & B3 & "'!$C$2:$L$13")
*(INDIRECT("'" & B3 & "'!$A$2:$A$13")='Loss Ratio'!$B$1)
*(INDIRECT("'" & B3 & "'!$B$2:$B$13")='Loss Ratio'!$B9)
*(INDIRECT("'" & B3 & "'!$C$1:$L$1")='Loss Ratio'!C$8)
)

It uses INDIRECT to adapt the address of the cells based on B3.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Ryckiej]

Thanks Saboh - that worked on my sample spreadsheet - now I'll plug it into the big one and see if I can get it working.