IFS, SUMIF and INDEX

[N0t Y0urs]

I have two formulas that work perfectly but I want to add another if (I think) element to the formula.

I have just got a small sample of data and based on the small sample its not worth trying to find a short cut but when you have 100 data sets and 500+ rows of data.

So what I am wanting is on the main sheet I want the values to transpose from the respective sheet that contains the data. I have the first 5 rows of formulas in and the function I want to add (and I not sure what it would be) is if C12 is 6 then search $B4:$V$4 in all additional sheets for 6 when it finds it then it performs the formula that I already have working?

Is that possible?

I did think a vlookup but my data will come from 5 different sheets and is being summarised into one based on the criteria that I am entering

Here is a link to the summary file I am using

Summary.xlsx

Main Main sheet Criteria,5 Number,Qty, Value , Total 1,157, $ 464.00 2,139, $ 779.00 3,203, $ 203.00 4,127, $ 127.00 5,150, $ 666.00 6 7 8 9 10

docs.google.com

 

[TylerWhieler]

For Cell D7:

=IFERROR(SUM(OFFSET(XLOOKUP($C7;Sheet2!$4:$4;Sheet2!$6:$6);0;-2;$C$4));SUM(OFFSET(XLOOKUP($C7;Sheet4!$4:$4;Sheet4!$6:$6);0;-2;$C$4)))

For Cell E7:

=IFERROR(SUM(OFFSET(XLOOKUP($C7;Sheet2!$4:$4;Sheet2!$6:$6);0;-1;$C$4));SUM(OFFSET(XLOOKUP($C7;Sheet4!$4:$4;Sheet4!$6:$6);0;-1;$C$4)))

For Cell F7:

=IFERROR(SUM(OFFSET(XLOOKUP($C7;Sheet2!$4:$4;Sheet2!$6:$6);0;0;$C$4));SUM(OFFSET(XLOOKUP($C7;Sheet4!$4:$4;Sheet4!$6:$6);0;0;$C$4)))

The only important note for this to work properly is that each data set needs to have a unique number.
You can keep adding IFERROR in front and just change the Sheetnumber for example:

=IFERROR(IFERROR(SUM(OFFSET(XLOOKUP($C7;Sheet2!$4:$4;Sheet2!$6:$6);0;-2;$C$4));SUM(OFFSET(XLOOKUP($C7;Sheet4!$4:$4;Sheet4!$6:$6);0;-2;$C$4)));SUM(OFFSET(XLOOKUP($C7;Sheet2!$4:$4;Sheet5!$6:$6);0;-2;$C$4)))

Hope it helps goodluck,

 

[N0t Y0urs]

Thanks will give it a shot. Each data set is unique it’s 1 - 100. Will let you know how I go.

Thanks

 

[N0t Y0urs]

Thanks, this works great for my first two columns but my third in the data set is to only report the value. I am now working backwards to understand the logic and how you got there.

Thanks

 

[TylerWhieler]

Hi,

Do I understand correctly that u want a different formula in column F7:F16?

If yes, which value do u want here?

 

[N0t Y0urs]

Yes and the value for that column is F7 - 285, F8 - 532 etc

Summary.xlsx
	B	C	D	E	F	G	H	I	J
2	Main sheet
3
4	Criteria	5
5		Data set	Qty	Value	Total		Current Formula that works
6
7		1	157	356		285	12432
8		2	139	779		532
9		3	203	334		5457
10		4	127	910		9954
11		5	150	666		1804
12		6	115	879
13		7	152	701
14		8	203	633
15		9	87	851
16		10	155	871
17
Main

Cell Formulas
Range	Formula
D7:D11	D7	=IFERROR(SUM(OFFSET(XLOOKUP($C7,Data!$4:$4,Data!$6:$6),0,-2,$C$4)),SUM(OFFSET(XLOOKUP($C$7,Data2!$4:$4,Data2!$6:$6),0,-2,$C$4)))
E7:E11	E7	=IFERROR(SUM(OFFSET(XLOOKUP($C7,Data!$4:$4,Data!$6:$6),0,-1,$C$4)),SUM(OFFSET(XLOOKUP($C$7,Data2!$4:$4,Data2!$6:$6),0,-1,$C$4)))
D12:D16	D12	=IFERROR(SUM(OFFSET(XLOOKUP($C12,Data!$4:$4,Data!$6:$6),0,-2,$C$4)),SUM(OFFSET(XLOOKUP($C12,Data2!$4:$4,Data2!$6:$6),0,-2,$C$4)))
E12:E16	E12	=IFERROR(SUM(OFFSET(XLOOKUP($C12,Data!$4:$4,Data!$6:$6),0,-1,$C$4)),SUM(OFFSET(XLOOKUP($C12,Data2!$4:$4,Data2!$6:$6),0,-1,$C$4)))
H7	H7	=INDEX(Data!F:F,MATCH($C$4,Data!$B:$B))

 

[N0t Y0urs]

Now I have completely confused myself and I think maybe my original explanation wasn't detailed enough. this is going to be long.

Using the formula you suggested I am getting a value that I can't figure out where it is coming from.

This is my dashboard

Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
14
15		53		Start	Profit	RB	Daily %	FWD	SWD	DB	RB	Difference
16		1	Acc1	$ 100.00	$ 40.91	$ 140.91	40.9%	$ 45,855.74
17		2	Acc2	$ 125.00		$ 125.00	0.0%
18		3	Acc3	$ 250.00		$ 250.00	0.0%
19		4	Acc4	$ 250.00		$ 250.00	0.0%
20		5	Acc5	$ 250.00		$ 250.00	0.0%
21		6	Acc6	$ 250.00		$ 250.00	0.0%
22		7	Acc7	$ 250.00		$ 250.00	0.0%
23		8	Acc8	$ 250.00		$ 250.00	0.0%
24		9	Acc9	$ 250.00		$ 250.00	0.0%
25		10	Acc10	$ 250.00		$ 250.00	0.0%
26		11	Acc11	$ 250.00		$ 250.00	0.0%
27		12	Acc12	$ 250.00		$ 250.00	0.0%
28		13	Acc13	$ 250.00		$ 250.00	0.0%
29		14	Acc14	$ 250.00		$ 250.00	0.0%
30		15	Acc15	$ 250.00		$ 250.00	0.0%
31		16	Acc16	$ 250.00		$ 250.00	0.0%
32		17	Acc17	$ 250.00		$ 250.00	0.0%
33		18	Acc18	$ 250.00		$ 250.00	0.0%
34		19	Acc19	$ 250.00		$ 250.00	0.0%
35		20	Acc20	$ 125.00		$ 125.00	0.0%
36		21	Acc21
37		22	Acc22
38		23	Acc23
39		24	Acc24
40		25	Acc25
Dashboard

Cell Formulas
Range	Formula
F16:F35	F16	=D16+E16
G16:G35	G16	=(F16-D16)/D16
H16	H16	=IFERROR(SUM(OFFSET(XLOOKUP($B16,Group1!$2:$2,Group1!$5:$5),0,-2,$B$15)),SUM(OFFSET(XLOOKUP($B16,Group2!$2:$2,Group2!$5:$5),0,-2,$B$15)))
D19:D34	D19	=D18
B17:B40	B17	=B16+1

So the value of $45,855.74 should be $4,125 as per the sheet that the data is being calculated from

Excel.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2			Data Group		1					Data Group		1					Data Group		1
3		1.0%		Data Set		1	$ 50.00		1.0%		Data Set		2	$ 125.00		1.0%		Data Set		3	$ 250.00
4	Asset	Size	Result	FWD	SWD	DB	RB		Size	Result	FWD	SWD	DB	RB		Size	Result	FWD	SWD	DB	RB
5	XAU	0.04	$ 17.80				$ 67.80
6	XAU	0.04	$ 17.80				$ 85.60
7	XAU	0.04	$ 17.80				$ 103.40
8	XAU	0.09	$ 35.60				$ 139.01
9	XAU	0.09	$ 35.60	$ -		$ -	$ 174.61
10	XAU	0.09	$ 35.60				$ 210.21
11	XAU	0.13	$ 53.40				$ 263.61
12	XAU	0.13	$ 53.40				$ 317.02
13	XAU	0.17	$ 71.20				$ 388.22
14	XAU	0.17	$ 71.20	$ 125.00		$ -	$ 334.43							$ 125.00
15	XAU	0.17	$ 71.20				$ 405.63		0.09	$ 35.60				$ 160.60
16	XAU	0.22	$ 89.01				$ 494.64		0.09	$ 35.60				$ 196.20
17	XAU	0.22	$ 89.01				$ 583.64		0.09	$ 35.60				$ 231.81
18	XAU	0.26	$ 106.81				$ 690.45		0.13	$ 53.40				$ 285.21
19	XAU	0.30	$ 124.61	$ 250.00		$ -	$ 565.06		0.13	$ 53.40	$ -		$ -	$ 338.61							$ 250.00
20	XAU	0.26	$ 106.81				$ 671.86		0.17	$ 71.20				$ 409.82		0.13	$ 53.40				$ 303.40
21	XAU	0.30	$ 124.61				$ 796.47		0.22	$ 89.01				$ 498.82		0.17	$ 71.20				$ 374.61
22	XAU	0.35	$ 142.41				$ 938.88		0.22	$ 89.01				$ 587.83		0.17	$ 71.20				$ 445.81
23	XAU	0.43	$ 178.01				$ 1,116.89		0.26	$ 106.81				$ 694.64		0.22	$ 89.01				$ 534.82
24	XAU	0.52	$ 213.61	$ 250.00		$ 135.06	$ 945.44		0.30	$ 124.61	$ 250.00		$ -	$ 569.24		0.26	$ 106.81	$ 125.00		$ -	$ 516.63
25	XAU	0.43	$ 178.01				$ 1,123.45		0.26	$ 106.81				$ 676.05		0.26	$ 106.81				$ 623.43
26	XAU	0.52	$ 213.61				$ 1,337.07		0.30	$ 124.61				$ 800.66		0.30	$ 124.61				$ 748.04
27	XAU	0.61	$ 249.22				$ 1,586.28		0.39	$ 160.21				$ 960.87		0.35	$ 142.41				$ 890.45
28	XAU	0.69	$ 284.82				$ 1,871.10		0.43	$ 178.01				$ 1,138.88		0.39	$ 160.21				$ 1,050.66
29	XAU	0.82	$ 338.22	$ 750.00		$ 182.42	$ 1,276.91		0.52	$ 213.61	$ 250.00		$ 137.81	$ 964.68		0.48	$ 195.81	$ 250.00		$ -	$ 996.47
30	XAU	0.56	$ 231.41				$ 1,508.32		0.43	$ 178.01				$ 1,142.69		0.43	$ 178.01				$ 1,174.48
31	XAU	0.69	$ 284.82				$ 1,793.14		0.52	$ 213.61				$ 1,356.31		0.52	$ 213.61				$ 1,388.10
32	XAU	0.78	$ 320.42				$ 2,113.56		0.61	$ 249.22				$ 1,605.52		0.61	$ 249.22				$ 1,637.31
33	XAU	0.95	$ 391.63				$ 2,505.19		0.74	$ 302.62				$ 1,908.14		0.74	$ 302.62				$ 1,939.93
34	XAU	1.13	$ 462.83	$ 750.00		$ 277.25	$ 1,940.76		0.87	$ 356.02	$ 750.00		$ 189.27	$ 1,324.90		0.87	$ 356.02	$ 750.00		$ 193.24	$ 1,352.71
35	XAU	0.87	$ 356.02				$ 2,296.79		0.61	$ 249.22				$ 1,574.11		0.61	$ 249.22				$ 1,601.93
36	XAU	1.00	$ 409.43				$ 2,706.21		0.69	$ 284.82				$ 1,858.93		0.74	$ 302.62				$ 1,904.55
37	XAU	1.21	$ 498.43				$ 3,204.65		0.82	$ 338.22				$ 2,197.15		0.87	$ 356.02				$ 2,260.57
38	XAU	1.43	$ 587.44				$ 3,792.08		0.95	$ 391.63				$ 2,588.78		1.00	$ 409.43				$ 2,670.00
39	XAU	1.65	$ 676.44	$ 750.00		$ 464.82	$ 3,253.71		1.13	$ 462.83	$ 750.00		$ 287.70	$ 2,013.91		1.17	$ 480.63	$ 750.00		$ 300.08	$ 2,100.55
40	XAU	1.43	$ 587.44				$ 3,841.15		0.91	$ 373.82				$ 2,387.73		0.95	$ 391.63				$ 2,492.17
41	XAU	1.69	$ 694.24				$ 4,535.39		1.04	$ 427.23				$ 2,814.96		1.08	$ 445.03				$ 2,937.20
42	XAU	2.00	$ 818.85				$ 5,354.25		1.26	$ 516.23				$ 3,331.19		1.30	$ 534.03				$ 3,471.24
43	XAU	2.34	$ 961.26				$ 6,315.51		1.48	$ 605.24				$ 3,936.43		1.52	$ 623.04				$ 4,094.28
44	XAU	2.78	$ 1,139.27	$ 1,250.00		$ 775.60	$ 5,429.18		1.74	$ 712.05	$ 750.00		$ 487.31	$ 3,411.17		1.78	$ 729.85	$ 750.00		$ 509.27	$ 3,564.86
45	XAU	2.39	$ 979.06				$ 6,408.25		1.52	$ 623.04				$ 4,034.21		1.56	$ 640.84				$ 4,205.70
46	XAU	2.82	$ 1,157.07				$ 7,565.32		1.78	$ 729.85				$ 4,764.05		1.87	$ 765.45				$ 4,971.15
47	XAU	3.30	$ 1,352.89				$ 8,918.21		2.08	$ 854.45				$ 5,618.51		2.17	$ 890.06				$ 5,861.20
Group1

Cell Formulas
Range	Formula
L3,Z3,S3	L3	=Tables!$I$16
P3	P3	=1
W3,AD3	W3	=P3+1
X3	X3	=N14
AE3	AE3	=N19
N9,AB44,U44,N44,AB39,U39,N39,AB34,U34,N34,AB29,U29,N29,AB24,U24,N24,U19,N19,N14	N9	=IFS(Q8+M9>15000,5000,Q8+M9>12500,2550,Q8+M9>5000,1250,Q8+M9>1750,750, Q8+M9>750,250,Q8+M9>400,125,Q8+M9<=399,0)
P9,AD44,W44,P44,AD39,W39,P39,AD34,W34,P34,AD29,W29,P29,AD24,W24,P24,W19,P19,P14	P9	=IFS(Q8+M9-N9>1000000,Q8+M9-N9-1000000,Q8+M9-N9>1000,(Q8+M9-N9)*Tables!$I$14,Q8+M9-N9>1,0)
K6:K47	K6	=K5
M5	M5	=SUMPRODUCT($L5*Tables!$I$9:$I$12*Tables!$I$5:$I$8*10*VLOOKUP(VLOOKUP(K5,Tables!$B$4:$C$42,2,),Tables!$E$4:$F$24,2,))
M6:M47,AA20:AA47,T15:T47	M6	=SUMPRODUCT(L6*Tables!$I$9:$I$12*Tables!$I$5:$I$8*10*VLOOKUP(VLOOKUP($K6,Tables!$B$4:$C$42,2,),Tables!$E$4:$F$24,2,))
Q5	Q5	=M5-N5-P5+Q3-O5
Q6:Q47,AE20:AE47,X15:X47	Q6	=Q5+M6-N6-O6-P6
X14,AE19	X14	=X$3

I then have an additional 4 sheets basically replicated with data starting on different rows which is why I want to be able to calculate this data.

So my goal is (I have only populated the first account)

Excel.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
13
14
15		60		Start	Profit	RB	Daily %	FWD	SWD	DB	RB	Difference
16		1	Acc1	$ 100.00	$ 40.91	$ 140.91	40.9%	$ 4,125.00	$ 16,250.00	$ 21,874.42	$ 66,351.94
17		2	Acc2	$ 125.00		$ 125.00	0.0%
18		3	Acc3	$ 250.00		$ 250.00	0.0%
Dashboard

Cell Formulas
Range	Formula
V16:V18	V16	=T16+U16
W16:W18	W16	=(V16-T16)/T16
AA16	AA16	=Group1!Q64
R17:R18	R17	=R16+1

Hope that makes sense

 

[N0t Y0urs]

So I have tried looking at this again and was wondering if I could do a multiple match formula with index.

What I was thinking was of using index then 3 matches but I don't know how to identify my additional matches

formula testing.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2
3				Data Set	1						Data Set	2						Data Set	3
4	Number	Summary	Qty	Value	Total	Value A	V2	V3		Qty	Value	Total	Value A	V2	V3		Qty	Value	Total	Value A	V2	V3
5	1		15	$ 55.00	$ 825.00					13	$ 105.00	$ 1,365.00					50	$ 60.00	$ 3,000.00
6	2		46	$ 90.00	$ 4,965.00					37	$ 101.00	$ 5,102.00					51	$ 98.00	$ 7,998.00
7	3		42	$ 52.00	$ 7,149.00					42	$ 209.00	$ 13,880.00					3	$ 76.00	$ 8,226.00
8	4		49	$ 102.00	$ 12,147.00					43	$ 231.00	$ 23,813.00					48	$ 45.00	$ 10,386.00
9	5		5	$ 57.00	$ 12,432.00	$ 2,500.00				4	$ 133.00	$ 24,345.00	$ 5,000.00				51	$ 55.00	$ 13,191.00	$ 2,500.00
10	6		48	$ 68.00	$ 15,696.00					3	$ 104.00	$ 24,657.00					37	$ 85.00	$ 16,336.00
11	7		28	$ 99.00	$ 18,468.00					16	$ 114.00	$ 26,481.00					22	$ 146.00	$ 19,548.00
12	8		51	$ 125.00	$ 24,843.00					18	$ 57.00	$ 27,507.00					32	$ 100.00	$ 22,748.00
13	9		13	$ 158.00	$ 26,897.00					4	$ 181.00	$ 28,231.00					32	$ 206.00	$ 29,340.00
14	10		17	$ 179.00	$ 29,940.00	$ 5,000.00				16	$ 115.00	$ 30,071.00		$ 5,000.00			43	$ 94.00	$ 33,382.00		$ 5,000.00
15	11		51	$ 90.00	$ 34,530.00					48	$ 139.00	$ 36,743.00					34	$ 64.00	$ 35,558.00
16	12		4	$ 150.00	$ 35,130.00					28	$ 197.00	$ 42,259.00					2	$ 174.00	$ 35,906.00
17	13		4	$ 151.00	$ 35,734.00					36	$ 90.00	$ 45,499.00					22	$ 129.00	$ 38,744.00
18	14		2	$ 171.00	$ 36,076.00					50	$ 163.00	$ 53,649.00					37	$ 182.00	$ 45,478.00
19	15		26	$ 68.00	$ 37,844.00		$ 5,000.00			39	$ 231.00	$ 62,658.00		$ 5,000.00			5	$ 223.00	$ 46,593.00		$ 5,000.00
20	16		42	$ 190.00	$ 45,824.00					43	$ 237.00	$ 72,849.00					15	$ 97.00	$ 48,048.00
21	17		21	$ 77.00	$ 47,441.00					17	$ 111.00	$ 74,736.00					52	$ 195.00	$ 58,188.00
22	18		24	$ 157.00	$ 51,209.00					2	$ 109.00	$ 74,954.00					16	$ 113.00	$ 59,996.00
23	19		1	$ 247.00	$ 51,456.00					29	$ 250.00	$ 82,204.00					45	$ 104.00	$ 64,676.00
24	20		22	$ 76.00	$ 53,128.00		$ 5,000.00			9	$ 61.00	$ 82,753.00		$ 5,000.00			9	$ 98.00	$ 65,558.00		$ 5,000.00
25	21		55	$ 103.00	$ 58,793.00					7	$ 229.00	$ 84,356.00					4	$ 110.00	$ 65,998.00
26	22		46	$ 198.00	$ 67,901.00					3	$ 124.00	$ 84,728.00					22	$ 138.00	$ 69,034.00
27	23		43	$ 176.00	$ 75,469.00					52	$ 70.00	$ 88,368.00					48	$ 60.00	$ 71,914.00
28	24		49	$ 114.00	$ 81,055.00					38	$ 231.00	$ 97,146.00					14	$ 99.00	$ 73,300.00
29	25		4	$ 97.00	$ 81,443.00		$ 5,000.00			5	$ 98.00	$ 97,636.00		$ 5,000.00			42	$ 182.00	$ 80,944.00		$ 5,000.00
30
31
32
33
34
35
36
37
38				Number	Data Set	Variable
39				5	2	Total
40			Match	9
41			Match	13
42			Index	0
43			All
44
45
46
47		CNumber	$E$39
48		Number	$B:$B
49		CDataSet	$F$39
50		DataSet	$D$5:$AK$30
51
52
53
Data

Cell Formulas
Range	Formula
M3,T3	M3	=F3+1
G9,G14,U9,N9	G9	=IFS(F9>15000,5000,F9>10000,2500,F9>5000,1250,F9<4999,0)
O14,V29,O29,H29,V24,O24,H24,V19,O19,H19,V14	O14	=IFS(M14>15000,5000,M14>10000,2500,M14>5000,1250,M14<4999,0)
B6:B29	B6	=B5+1
F5,T5,M5	F5	=D5*E5
F6:F29,T6:T29,M6:M29	F6	=(D6*E6)+F5
E40	E40	=MATCH(CNumber,Number,0)
E41	E41	=MATCH(CDataSet,DataSet,0)
E42	E42	=INDEX(Data2,Blue,Pink)

Named Ranges
Name	Refers To	Cells
Blue	=Data!$E$40	E42
CDataSet	=Data!$F$39	E41
CNumber	=Data!$E$39	E40
Data2	=Data!$D$5:$AL$29	E42, F5
DataSet	=Data!$3:$3	E41
Number	=Data!$B:$B	E40
Pink	=Data!$E$41	E42

So my index match works fine for the one data set but I ultimately have 100 different sets across 5 different pages so need to find a way to do this.

Any direction would be wonderful