More Robust Formula

[Peggy2015]

Hello,

We have a spreadsheet that is referencing data based on the criteria if it says 'Flat Cash' or 'Projects':

P5B	IS Facilities
		FINANCIAL FORECASTING 2016/17
	Analysis by Flat Cash / Project	Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast
	Net Expenditure	1	2	3	4	5	6	7	8	9	10	11	12
	Flat Cash	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397
	Projects	0	0	0	0	0	0	0	0	0	0	0	0
	Net Expenditure	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397

<tbody>
</tbody><colgroup><col><col><col span="12"></colgroup>

This is the formula in the cell C5 for the value: 35,388:

=IF(VLOOKUP($A$35,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$35,0)+IF(VLOOKUP($A$36,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$36,0)+IF(VLOOKUP($A$37,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$37,0)+IF(VLOOKUP($A$38,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$38,0)+IF(VLOOKUP($A$39,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$39,0)+IF(VLOOKUP($A$40,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$40,0)+IF(VLOOKUP($A$41,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$41,0)+IF(VLOOKUP($A$42,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$42,0)

In the Input Data tab we have a list of codes to lookup to see if they are noted as 'Flat cash' or 'projects'

T1A000	Flat Cash
T8A000	Flat Cash
T1B510	Flat Cash
T1B540	Flat Cash
T1B542	Flat Cash
TSA001	Flat Cash
U9A000	Flat Cash
U9B573	Flat Cash
M9T000	Projects

<colgroup><col width="64" style="width: 48pt;" span="2">
<tbody>




































</tbody>

if the numbers are flat cash I need them to total up the amount in the next table and give me the total number, same for projects. this is the table where the numbers are pulling from:

P5B	IS Facilities
		FINANCIAL FORECASTING 2016/17
	Analysis by Job Code	Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast
	Net Expenditure	1	2	3	4	5	6	7	8	9	10	11	12
T1A000	GENERAL IS OPERATING COSTS	3,939	988	1,187	1,187	1,187	1,187	1,186	1,186	1,189	1,189	1,189	1,189
T8A000	GENERAL SERVICE PROVISION	0	0	0	0	0	0	0	0	0	0	0	0
T1B510	FLEET VEHICLES	337	126	0	0	0	0	0	0	0	0	0	0
T1B540	ML SECURITY	0	0	0	0	0	0	5,500	0	0	0	0	0
T1B542	OTHER SITE SECURITY	0	0	0	0	0	0	750	0	0	0	0	0
TSA001	IS SALARY CHARGES	31,112	33,654	33,208	33,208	33,208	33,208	33,208	33,207	33,207	33,208	33,208	33,208
U9A000	GENERAL COMPUTING EQUIPMENT	0	0	0	0	0	0	0	0	0	0	0	0
U9B573	OCCUPATIONAL HEALTH EQUIPMENT	0	0	0	0	0	0	300	0	0	0	0	0
	Net Expenditure	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397

<tbody>
</tbody><colgroup><col><col><col span="12"></colgroup>

As you can see from the above vlookup formula it is far too long. Do you have any suggestions of how this could be condensed and made more robust?

Thank you,
Peggy

 

[AliGW]

What do you mean by 'more robust'? Are you in fact just looking for a shorter formula that does the same job, or is there something about the formula you are using that does not work?

 

[Peggy2015]

Hi Ali,

Yes to both questions! The formula is being very temperamental - it doesn't like adding up the Projects totals. Ideally a shorter formula would be a big help!

Thanks,
Peggy

 

[AlanY]

are you happy with a helper column (that can be hidden), if so, try this

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	T1A000	Flat Cash	GENERAL IS OPERATING COSTS	3,939	988	1,187	1,187	1,187	1,187	1,186	1,186	1,189	1,189	1,189	1,189
2	T8A000	Flat Cash	GENERAL SERVICE PROVISION	0	0	0	0	0	0	0	0	0	0	0	0
3	T1B510	Flat Cash	FLEET VEHICLES	337	126	0	0	0	0	0	0	0	0	0	0
4	T1B540	Flat Cash	ML SECURITY	0	0	0	0	0	0	5,500	0	0	0	0	0
5	T1B542	Flat Cash	OTHER SITE SECURITY	0	0	0	0	0	0	750	0	0	0	0	0
6	TSA001	Flat Cash	IS SALARY CHARGES	31,112	33,654	33,208	33,208	33,208	33,208	33,208	33,207	33,207	33,208	33,208	33,208
7	U9A000	Flat Cash	GENERAL COMPUTING EQUIPMENT	0	0	0	0	0	0	0	0	0	0	0	0
8	U9B573	Flat Cash	OCCUPATIONAL HEALTH EQUIPMENT	0	0	0	0	0	0	300	0	0	0	0	0
9	M9T000	Projects	Testing	12,345			12,345
10			Net Expenditure	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397
11			Flat Cash	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397
12			Projects	12,345	0	0	12,345	0	0	0	0	0	0	0	0
13
14	T1A000	Flat Cash
15	T8A000	Flat Cash
16	T1B510	Flat Cash
17	T1B540	Flat Cash
18	T1B542	Flat Cash
19	TSA001	Flat Cash
20	U9A000	Flat Cash
21	U9B573	Flat Cash
22	M9T000	Projects
Sheet2

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,$A$14:$B$22,2,0)
D11	=SUMIF($B$1:$B$9,$C11,D$1:D$9)

 

[Peggy2015]

are you happy with a helper column (that can be hidden), if so, try this

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	T1A000	Flat Cash	GENERAL IS OPERATING COSTS	3,939	988	1,187	1,187	1,187	1,187	1,186	1,186	1,189	1,189	1,189	1,189
2	T8A000	Flat Cash	GENERAL SERVICE PROVISION	0	0	0	0	0	0	0	0	0	0	0	0
3	T1B510	Flat Cash	FLEET VEHICLES	337	126	0	0	0	0	0	0	0	0	0	0
4	T1B540	Flat Cash	ML SECURITY	0	0	0	0	0	0	5,500	0	0	0	0	0
5	T1B542	Flat Cash	OTHER SITE SECURITY	0	0	0	0	0	0	750	0	0	0	0	0
6	TSA001	Flat Cash	IS SALARY CHARGES	31,112	33,654	33,208	33,208	33,208	33,208	33,208	33,207	33,207	33,208	33,208	33,208
7	U9A000	Flat Cash	GENERAL COMPUTING EQUIPMENT	0	0	0	0	0	0	0	0	0	0	0	0
8	U9B573	Flat Cash	OCCUPATIONAL HEALTH EQUIPMENT	0	0	0	0	0	0	300	0	0	0	0	0
9	M9T000	Projects	Testing	12,345			12,345
10			Net Expenditure	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397
11			Flat Cash	35,388	34,768	34,395	34,395	34,395	34,395	40,944	34,393	34,396	34,397	34,397	34,397
12			Projects	12,345	0	0	12,345	0	0	0	0	0	0	0	0
13
14	T1A000	Flat Cash
15	T8A000	Flat Cash
16	T1B510	Flat Cash
17	T1B540	Flat Cash
18	T1B542	Flat Cash
19	TSA001	Flat Cash
20	U9A000	Flat Cash
21	U9B573	Flat Cash
22	M9T000	Projects

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =VLOOKUP(A1,$A$14:$B$22,2,0) D11 =SUMIF($B$1:$B$9,$C11,D$1:D$9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi AlanY,
Thanks for the help with the formulas. This is working much better now!!
Best wishes,
Peggy

 

[AlanY]

you're welcome