Sum and subtotal filtered results for multiple sheets/rows/columns

[lrawhite]

Hi all--
I have an extensive formula that works well, until I filter the results. Hope someone can give me some assistance in shortening the formula (if possible) as well as getting the filtered results.


Here is a sample of the formula that I am currently using:
=IF($A2="",0,SUM(SUMPRODUCT(--(Sheet 1[Company]=$A2),Sheet 1[Income]),SUMPRODUCT(--(Sheet 1[Company]=$A2),Sheet 1[Wages]),SUMPRODUCT(--(Sheet 1[Company]=$A2),Sheet 1[Merchandise]),SUMPRODUCT(--(Sheet 1[Company]=$A2),Sheet 1[Rent]),SUMPRODUCT(--(Sheet 2[Company]=$A2),Sheet 2[Income]),SUMPRODUCT(--(Sheet 2[Company]=$A2),Sheet 2[Wages]),SUMPRODUCT(--(Sheet 2[Company]=$A2),Sheet 2[Merchandise]),SUMPRODUCT(--(Sheet 2[Company]=$A2),Sheet 2[Merchandise]),SUMPRODUCT(--(Sheet 2[Company]=$A2),Sheet 2[Rent]),SUMPRODUCT(--(Sheet 3[Company]=$A2),Sheet 3[Income]),SUMPRODUCT(--(Sheet 3[Company]=$A2),Sheet 3[Wages]),SUMPRODUCT(--(Sheet 3[Company]=$A2),Sheet 3[Merchandise]),

Please note that this is a small portion of the formula.

Any help would be greatly appreciated.

Thank you in advance.

 

[shift-del]

Irawihite,

could you provide a link to an example workbook with the desired results?

 

[lrawhite]

https://onedrive.live.com/edit.aspx...897C!830&parId=B1265CC8964D897C!103&app=Excel

I hope this is the correct way to add the link. This is the first time I've posted a link to this site.

Thank you for all your help.

 

[shift-del]

Irawhite,

the file is not available.

 

[lrawhite]

I am unsure how to attach an example workbook. Can you please help?