SUM across multiple sheets and multiple by weight

[br0nc0boy]

Hi everyone I am doing an exercise where I am trying to sum across multiple tabs based on the drop down. Summing part I know how to do w/ INDIRECT i.e. =SUM(INDIRECT("'"&Tabs&"'!"&CELL("address")))
where "Tabs" is defined name range.

the curve ball is that each of those tabs has different weight (not sure weight is the right word, but it's more of the company's controlling interest)

so below is very rough example, we would get $1,264 if we apply the weight

Tab1 $1,000 100%
Tab2 $500 33%
Tab3 $300 33%

but it's not just one number, it's the entire P&L and there are several portfolios (based on the drop down they will look at different tabs), so I am looking for perhaps one formula that can solve this problem.

Many thanks in advance.

 

[etaf]

an example of the layout may help , us with specific
but to get the weighting

=SUMPRODUCT(Value Range, percent range)

i look at an example with a tab and dropdown

not sure how you are getting the address ranges to lookup exactly, so hardcoded in my example

date-text-etaf.xlsx
	A	B	C	D	E
14
15	TABS Dropdown	sheet2
16
17			1264		SUMPRODUCT(INDIRECT("'"&B15&"'!A2:A5"),INDIRECT("'"&B15&"'!B2:B5"))
18
19
Sheet1

Cell Formulas
Range	Formula
C17	C17	=SUMPRODUCT(INDIRECT("'"&B15&"'!A2:A5"),INDIRECT("'"&B15&"'!B2:B5"))

date-text-etaf.xlsx
	A	B
1
2	1000	1
3	500	0.33
4	300	0.33
Sheet2

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[br0nc0boy]

Hi @etaf Totally new to posting, but how do I upload the file? I've included the dropbox link. Let me know if you can see the example. thank you for the help.

 

[etaf]

i'm not sure what the summary stabilised means

so assuming the proportionate means apply the value in B1
then
=IF($B$2="yes",INDIRECT("'"&Summary!$B$1&"'!"&ADDRESS(ROW(),COLUMN()))*INDIRECT("'"&Summary!$B$1&"'!$B$1"),INDIRECT("'"&Summary!$B$1&"'!"&ADDRESS(ROW(),COLUMN())))

But thats not the same as you showed originally

Best way is to provide the link as you did
OR use XL2BB

heres the updated file on dropbox
hopefully its close to what you need - otherwise more explanation is needed

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

XL2BB - summary and one tab example

example-proportion-ETAF.xlsx
	A	B	C	D
1	Portfolio	SS (JV1)	<<< idea is to have this drop down control what tabs get SUM
2	Proportionate	No
3
4		Same Store	Stabilized
5	Sales	500	1,200
6	COGS	300	600
7	Gross Profit	200	600
8		0
9	Expenses	100	300
10	Net Income	100	300
11
Summary

Cell Formulas
Range	Formula
C5:C6,C9	C5	=SUMPRODUCT('JV1 Stab'!B5,'JV1 Stab'!$B$1)+SUMPRODUCT('JV2 Stab'!B5,'JV2 Stab'!$B$1)+SUMPRODUCT('JV3 Stab'!B5,'JV3 Stab'!$B$1)
C7	C7	=C5-C6
B5:B10	B5	=IF($B$2="yes",INDIRECT("'"&Summary!$B$1&"'!"&ADDRESS(ROW(),COLUMN()))*INDIRECT("'"&Summary!$B$1&"'!$B$1"),INDIRECT("'"&Summary!$B$1&"'!"&ADDRESS(ROW(),COLUMN())))
C10	C10	=C7-C9

Cells with Data Validation
Cell	Allow	Criteria
B1	List	Total Same Store,SS (Own),SS (JV1),Total Stabilized,JV1 Stab,JV2 Stab,JV3 Stab
B2	List	Yes,No

example-proportion-ETAF.xlsx
	A	B
1	Company's Controlling Interest	100%
2
3
4		P&L
5	Sales	500
6	COGS	300
7	Gross Profit	200
8
9	Expenses	100
10	Net Income	100
11
SS (JV1)

Cell Formulas
Range	Formula
B1	B1	=IF(Summary!$B$2="Yes",30%,1)
B7	B7	=B5-B6
B10	B10	=B7-B9