Sum Lookup

[Bigbz]

Hi All,


Im trying to automate a dash board for financial reporting across branches. This is what mu dashboard looks like:

Date	Nov 16
	Shop 1	Shop 2
Sales	54000	45000
Purchases	12500	24000

<tbody>
</tbody>

I then have a tab for each branch which looks like this:

Shop 1	June 16	Jul 16	Aug 16
Sales	12500	10000	20000
Purchases	5000	2000	4500
Wages	1500	2000	3000

<tbody>
</tbody>

Shop 1 would be the name of the tab.

What formula should i use on the dashboard that looks up the branch tab, then looks up the line name eg sales, then sums the monthly data for the year up until l the date on the dashboard?


Thanks in advance.

 

[WarfritLive]

=SUM('Shop 1'!B2:XXX2)

Edit: Just saw the date part, sorry.
How about
=SUMPRODUCT(('Shop 1'!B2:XXX2),--('Shop 1'!A2:XXX2<=B1))

I'm not at my computer so I can't double check it but I think it should work. B1 is the date cell

 

[Bigbz]

Hi,

Thanks for your reply. I need to to be a bit more dynamic as the information on the branch tabs is a copy and paste from accounting software each month. The sales or purchase lines can change each month, depending on the transactions for the month. Is it also possible to have a dynamic reference to the tab name so I can copy the formula across on the dashboard?

Cheers

 

[Bigbz]

Some pictures might make this clearer. The "Dashboard" is where I am trying to enter the formula to lookup and sum from each branch. I need to lookup the "Branch 1" tab, look uo the expense line, then sum across the values to the date on the dashboard (C3). I am trying to enter a formula into cell C10 to do this. I have tried, sumif, vlookups ect with no success.

Thanks fort your help

​

	A	B	C	D	E
1
2		Finance Dashboard
3		Actuals to	Sep-16
4
5			Branch 1	Branch 2	Branch 3
6
7
8
9	Expense
10	Animal Health total
11	Breeding total
12	Calf Rearing total
13	Other Animal total
14	Cleaning
15	Other Feed total
16	Fertiliser total
17	Weed & Pest total
18	Pasture Renewal total
19	Repairs and Maintenance total
20	Electricity total
21	Shed Expenses total
22	Staff Costs total
23	Vehicle Expenses total
24	Other Operations total
25	Non Operating total

<tbody>
</tbody>

Dashboard​

This is a sample of each branch profit & loss report. Its important to note that the expense items are dynamic in each branch, and change every month. The tab name for each branch P & L is "Branch 1" and "Branch 2"

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
8		Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Total
46	Animal Health total	179	5,092	12,316	15,552	14,474	6,470	2,102	1,384	-	-	-	-	57,569
47	Breeding
48	Breeding Expenses	-	-	-	-	6,930	7,181	-	-	-	-	-	-	14,111
49	Breeding total	-	-	-	-	6,930	7,181	-	-	-	-	-	-	14,111
50	Calf Rearing
51	Calf Rearing Expenses	1,379	4,890	948	-	-	-	-	-	-	-	-	-	7,217
52	Calf Rearing total	1,379	4,890	948	-	-	-	-	-	-	-	-	-	7,217
53	Other Animal
54	Animal Identification	547	4,768	311	709	1,361	898	2,339	619	-	-	-	-	11,552
55	Other Animal total	547	4,768	311	709	1,361	898	2,339	619	-	-	-	-	11,552
56	Grazing
57	Grazing Expenses	-	-	4,343	2,364	3,612	3,510	3,612	4,225	-	-	-	-	21,666
58	Grazing total	-	-	4,343	2,364	3,612	3,510	3,612	4,225	-	-	-	-	21,666
59	Other Feed
60	Maize & Grass Silage	-	-	17,767	-	-	-	-6,432	-	-	-	-	-	11,335
61	Other - Feed	10,080	1,420	19,501	-	-	-	-	-	-	-	-	-	31,001
62	PKE	-	4,047	26,167	12,686	44,962	28,628	7,242	8,582	4,556	-	-	-	136,870
63	Other Feed total	10,080	5,467	63,435	12,686	44,962	28,628	810	8,582	4,556	-	-	-	179,206
64	Fertiliser
65	Fertiliser	-	-	-	15,721	10,127	1,068	45,098	-	-	-	-	-	72,014
66	Fertiliser total	-	-	-	15,721	10,127	1,068	45,098	-	-	-	-	-	72,014
67	Weed & Pest
68	Weeds & Pests	-	-	2,677	2,429	-	7,804	7,847	-	-	-	-	-	20,757
69	Weed & Pest total	-	-	2,677	2,429	-	7,804	7,847	-	-	-	-	-	20,757
70	Pasture Renewal
71	Repairs and Maintenance

<tbody>
</tbody>

Branch 1




​

 

[FDibbins]

From the sounds of it, you are not actually summing anything, you just want to bring back data based specific criteria. See if this will work for you...

	A​	B​	C​	D​
1​	​	Finance Dashboard	​	​
2​	​	Actuals to	9/1/2016​	​
3​	​	​	​	​
4​	​	Branch 1​	Branch 2​	Branch 3​
5​	Expense	​	​	​
6​	Animal Health total	15552​	​	​
7​	Breeding total	-​	​	​
8​	Calf Rearing total	-​	​	​
9​	Other Animal total	709​	​	​
10​	Cleaning	​	​	​
11​	Other Feed total	12686​	​	​
12​	Fertiliser total	15721​	​	​
13​	Weed & Pest total	2429​	​	​
14​	Pasture Renewal total	​	​	​
15​	Repairs and Maintenance total	​	​	​
16​	Electricity total	​	​	​
17​	Shed Expenses total	​	​	​
18​	Staff Costs total	​	​	​
19​	Vehicle Expenses total	​	​	​
20​	Other Operations total	​	​	​
21​	Non Operating total	​	​	​

B6=IFERROR(INDEX('Branch 1'!$B$2:$M$27,MATCH($A6,'Branch 1'!$A$2:$A$27,0),MATCH($C$2,'Branch 1'!$B$1:$M$1,0)),"")
copied down

All you then need to do is change the BOLDED reference for each sheet. I could put something together that would use B4:D4 to pull from the specific sheet, using INDIRECT(), but you probably don't need to get that fancy

 

[Bigbz]

Thanks Ford,

That's quite helpful. However, I am wanting to sum the amounts on the Branch 1 tab, up to and including the selected month (sum June, July, August & September amounts).

How do I change the formula to do this?

 

[FDibbins]

OK try this...

	A​	B​	C​
1​	​	Finance Dashboard	​
2​	​	Actuals to	9/1/2016​
3​	​	​	​
4​	​	​	Branch 1​
5​	Expense	​	​
6​	Animal Health total	33139​	​
7​	Breeding total	0​	​
8​	Calf Rearing total	7217​	​
9​	Other Animal total	6335​	​
10​	Cleaning	0​	​
11​	Other Feed total	91668​	​
12​	Fertiliser total	15721​	​
13​	Weed & Pest total	5106​	​
14​	Pasture Renewal total	0​	​
15​	Repairs and Maintenance total	0​	​
16​	Electricity total	0​	​
17​	Shed Expenses total	0​	​
18​	Staff Costs total	0​	​
19​	Vehicle Expenses total	0​	​
20​	Other Operations total	0​	​
21​	Non Operating total	0​	​

B6=SUMPRODUCT(--('Branch 1'!$B$1:$M$1<=$C$2)*('Branch 1'!$A$2:$A$27=$A9),('Branch 1'!$B$2:$M$27))

 

[Bigbz]

That works perfectly, Thanks very much.

 

[FDibbins]

Good stuff, happy to help