IFS, SUMIF and INDEX

N0t Y0urs

Board Regular
Joined
May 1, 2022
Messages
93
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. MacOS
  2. Mobile
  3. Web
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

 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
For Cell D7:
Excel Formula:
=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:
Excel Formula:
=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:
Excel Formula:
=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:
Excel Formula:
=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,
 
Upvote 0
Thanks will give it a shot. Each data set is unique it’s 1 - 100. Will let you know how I go.

Thanks
 
Upvote 0
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
 
Upvote 0
Hi,

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

If yes, which value do u want here?
 
Upvote 0
Yes and the value for that column is F7 - 285, F8 - 532 etc

Summary.xlsx
BCDEFGHIJ
2Main sheet
3
4Criteria5
5Data setQty Value Total Current Formula that works
6
7115735628512432
82139779532
932033345457
1041279109954
1151506661804
126115879
137152701
148203633
15987851
1610155871
17
Main
Cell Formulas
RangeFormula
D7:D11D7=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:E11E7=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:D16D12=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:E16E12=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)))
H7H7=INDEX(Data!F:F,MATCH($C$4,Data!$B:$B))
 
Upvote 0
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
ABCDEFGHIJKLMNO
14
1553StartProfitRBDaily %FWDSWDDBRBDifference
161Acc1$ 100.00$ 40.91$ 140.9140.9%$ 45,855.74
172Acc2$ 125.00$ 125.000.0%
183Acc3$ 250.00$ 250.000.0%
194Acc4$ 250.00$ 250.000.0%
205Acc5$ 250.00$ 250.000.0%
216Acc6$ 250.00$ 250.000.0%
227Acc7$ 250.00$ 250.000.0%
238Acc8$ 250.00$ 250.000.0%
249Acc9$ 250.00$ 250.000.0%
2510Acc10$ 250.00$ 250.000.0%
2611Acc11$ 250.00$ 250.000.0%
2712Acc12$ 250.00$ 250.000.0%
2813Acc13$ 250.00$ 250.000.0%
2914Acc14$ 250.00$ 250.000.0%
3015Acc15$ 250.00$ 250.000.0%
3116Acc16$ 250.00$ 250.000.0%
3217Acc17$ 250.00$ 250.000.0%
3318Acc18$ 250.00$ 250.000.0%
3419Acc19$ 250.00$ 250.000.0%
3520Acc20$ 125.00$ 125.000.0%
3621Acc21
3722Acc22
3823Acc23
3924Acc24
4025Acc25
Dashboard
Cell Formulas
RangeFormula
F16:F35F16=D16+E16
G16:G35G16=(F16-D16)/D16
H16H16=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:D34D19=D18
B17:B40B17=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
KLMNOPQRSTUVWXYZAAABACADAE
1
2Data Group1Data Group1Data Group1
31.0% Data Set 1$ 50.001.0% Data Set 2$ 125.001.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.630.09$ 35.60$ 160.60
16 XAU 0.22$ 89.01$ 494.640.09$ 35.60$ 196.20
17 XAU 0.22$ 89.01$ 583.640.09$ 35.60$ 231.81
18 XAU 0.26$ 106.81$ 690.450.13$ 53.40$ 285.21
19 XAU 0.30$ 124.61$ 250.00$ -$ 565.060.13$ 53.40$ -$ -$ 338.61$ 250.00
20 XAU 0.26$ 106.81$ 671.860.17$ 71.20$ 409.820.13$ 53.40$ 303.40
21 XAU 0.30$ 124.61$ 796.470.22$ 89.01$ 498.820.17$ 71.20$ 374.61
22 XAU 0.35$ 142.41$ 938.880.22$ 89.01$ 587.830.17$ 71.20$ 445.81
23 XAU 0.43$ 178.01$ 1,116.890.26$ 106.81$ 694.640.22$ 89.01$ 534.82
24 XAU 0.52$ 213.61$ 250.00$ 135.06$ 945.440.30$ 124.61$ 250.00$ -$ 569.240.26$ 106.81$ 125.00$ -$ 516.63
25 XAU 0.43$ 178.01$ 1,123.450.26$ 106.81$ 676.050.26$ 106.81$ 623.43
26 XAU 0.52$ 213.61$ 1,337.070.30$ 124.61$ 800.660.30$ 124.61$ 748.04
27 XAU 0.61$ 249.22$ 1,586.280.39$ 160.21$ 960.870.35$ 142.41$ 890.45
28 XAU 0.69$ 284.82$ 1,871.100.43$ 178.01$ 1,138.880.39$ 160.21$ 1,050.66
29 XAU 0.82$ 338.22$ 750.00$ 182.42$ 1,276.910.52$ 213.61$ 250.00$ 137.81$ 964.680.48$ 195.81$ 250.00$ -$ 996.47
30 XAU 0.56$ 231.41$ 1,508.320.43$ 178.01$ 1,142.690.43$ 178.01$ 1,174.48
31 XAU 0.69$ 284.82$ 1,793.140.52$ 213.61$ 1,356.310.52$ 213.61$ 1,388.10
32 XAU 0.78$ 320.42$ 2,113.560.61$ 249.22$ 1,605.520.61$ 249.22$ 1,637.31
33 XAU 0.95$ 391.63$ 2,505.190.74$ 302.62$ 1,908.140.74$ 302.62$ 1,939.93
34 XAU 1.13$ 462.83$ 750.00$ 277.25$ 1,940.760.87$ 356.02$ 750.00$ 189.27$ 1,324.900.87$ 356.02$ 750.00$ 193.24$ 1,352.71
35 XAU 0.87$ 356.02$ 2,296.790.61$ 249.22$ 1,574.110.61$ 249.22$ 1,601.93
36 XAU 1.00$ 409.43$ 2,706.210.69$ 284.82$ 1,858.930.74$ 302.62$ 1,904.55
37 XAU 1.21$ 498.43$ 3,204.650.82$ 338.22$ 2,197.150.87$ 356.02$ 2,260.57
38 XAU 1.43$ 587.44$ 3,792.080.95$ 391.63$ 2,588.781.00$ 409.43$ 2,670.00
39 XAU 1.65$ 676.44$ 750.00$ 464.82$ 3,253.711.13$ 462.83$ 750.00$ 287.70$ 2,013.911.17$ 480.63$ 750.00$ 300.08$ 2,100.55
40 XAU 1.43$ 587.44$ 3,841.150.91$ 373.82$ 2,387.730.95$ 391.63$ 2,492.17
41 XAU 1.69$ 694.24$ 4,535.391.04$ 427.23$ 2,814.961.08$ 445.03$ 2,937.20
42 XAU 2.00$ 818.85$ 5,354.251.26$ 516.23$ 3,331.191.30$ 534.03$ 3,471.24
43 XAU 2.34$ 961.26$ 6,315.511.48$ 605.24$ 3,936.431.52$ 623.04$ 4,094.28
44 XAU 2.78$ 1,139.27$ 1,250.00$ 775.60$ 5,429.181.74$ 712.05$ 750.00$ 487.31$ 3,411.171.78$ 729.85$ 750.00$ 509.27$ 3,564.86
45 XAU 2.39$ 979.06$ 6,408.251.52$ 623.04$ 4,034.211.56$ 640.84$ 4,205.70
46 XAU 2.82$ 1,157.07$ 7,565.321.78$ 729.85$ 4,764.051.87$ 765.45$ 4,971.15
47 XAU 3.30$ 1,352.89$ 8,918.212.08$ 854.45$ 5,618.512.17$ 890.06$ 5,861.20
Group1
Cell Formulas
RangeFormula
L3,Z3,S3L3=Tables!$I$16
P3P3=1
W3,AD3W3=P3+1
X3X3=N14
AE3AE3=N19
N9,AB44,U44,N44,AB39,U39,N39,AB34,U34,N34,AB29,U29,N29,AB24,U24,N24,U19,N19,N14N9=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,P14P9=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:K47K6=K5
M5M5=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:T47M6=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,))
Q5Q5=M5-N5-P5+Q3-O5
Q6:Q47,AE20:AE47,X15:X47Q6=Q5+M6-N6-O6-P6
X14,AE19X14=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
QRSTUVWXYZAAAB
13
14
1560StartProfitRBDaily %FWDSWDDBRBDifference
161Acc1$ 100.00$ 40.91$ 140.9140.9%$ 4,125.00$ 16,250.00$ 21,874.42$ 66,351.94
172Acc2$ 125.00$ 125.000.0%
183Acc3$ 250.00$ 250.000.0%
Dashboard
Cell Formulas
RangeFormula
V16:V18V16=T16+U16
W16:W18W16=(V16-T16)/T16
AA16AA16=Group1!Q64
R17:R18R17=R16+1



Hope that makes sense
 
Upvote 0
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
BCDEFGHIJKLMNOPQRSTUVW
2
3Data Set1Data Set2Data Set3
4NumberSummaryQtyValueTotalValue AV2V3QtyValueTotalValue AV2V3QtyValueTotalValue AV2V3
5115$ 55.00$ 825.0013$ 105.00$ 1,365.0050$ 60.00$ 3,000.00
6246$ 90.00$ 4,965.0037$ 101.00$ 5,102.0051$ 98.00$ 7,998.00
7342$ 52.00$ 7,149.0042$ 209.00$ 13,880.003$ 76.00$ 8,226.00
8449$ 102.00$ 12,147.0043$ 231.00$ 23,813.0048$ 45.00$ 10,386.00
955$ 57.00$ 12,432.00$ 2,500.004$ 133.00$ 24,345.00$ 5,000.0051$ 55.00$ 13,191.00$ 2,500.00
10648$ 68.00$ 15,696.003$ 104.00$ 24,657.0037$ 85.00$ 16,336.00
11728$ 99.00$ 18,468.0016$ 114.00$ 26,481.0022$ 146.00$ 19,548.00
12851$ 125.00$ 24,843.0018$ 57.00$ 27,507.0032$ 100.00$ 22,748.00
13913$ 158.00$ 26,897.004$ 181.00$ 28,231.0032$ 206.00$ 29,340.00
141017$ 179.00$ 29,940.00$ 5,000.0016$ 115.00$ 30,071.00$ 5,000.0043$ 94.00$ 33,382.00$ 5,000.00
151151$ 90.00$ 34,530.0048$ 139.00$ 36,743.0034$ 64.00$ 35,558.00
16124$ 150.00$ 35,130.0028$ 197.00$ 42,259.002$ 174.00$ 35,906.00
17134$ 151.00$ 35,734.0036$ 90.00$ 45,499.0022$ 129.00$ 38,744.00
18142$ 171.00$ 36,076.0050$ 163.00$ 53,649.0037$ 182.00$ 45,478.00
191526$ 68.00$ 37,844.00$ 5,000.0039$ 231.00$ 62,658.00$ 5,000.005$ 223.00$ 46,593.00$ 5,000.00
201642$ 190.00$ 45,824.0043$ 237.00$ 72,849.0015$ 97.00$ 48,048.00
211721$ 77.00$ 47,441.0017$ 111.00$ 74,736.0052$ 195.00$ 58,188.00
221824$ 157.00$ 51,209.002$ 109.00$ 74,954.0016$ 113.00$ 59,996.00
23191$ 247.00$ 51,456.0029$ 250.00$ 82,204.0045$ 104.00$ 64,676.00
242022$ 76.00$ 53,128.00$ 5,000.009$ 61.00$ 82,753.00$ 5,000.009$ 98.00$ 65,558.00$ 5,000.00
252155$ 103.00$ 58,793.007$ 229.00$ 84,356.004$ 110.00$ 65,998.00
262246$ 198.00$ 67,901.003$ 124.00$ 84,728.0022$ 138.00$ 69,034.00
272343$ 176.00$ 75,469.0052$ 70.00$ 88,368.0048$ 60.00$ 71,914.00
282449$ 114.00$ 81,055.0038$ 231.00$ 97,146.0014$ 99.00$ 73,300.00
29254$ 97.00$ 81,443.00$ 5,000.005$ 98.00$ 97,636.00$ 5,000.0042$ 182.00$ 80,944.00$ 5,000.00
30
31
32
33
34
35
36
37
38NumberData SetVariable
3952Total
40Match9
41Match 13
42Index0
43All
44
45
46
47CNumber$E$39
48Number$B:$B
49CDataSet$F$39
50DataSet$D$5:$AK$30
51
52
53
Data
Cell Formulas
RangeFormula
M3,T3M3=F3+1
G9,G14,U9,N9G9=IFS(F9>15000,5000,F9>10000,2500,F9>5000,1250,F9<4999,0)
O14,V29,O29,H29,V24,O24,H24,V19,O19,H19,V14O14=IFS(M14>15000,5000,M14>10000,2500,M14>5000,1250,M14<4999,0)
B6:B29B6=B5+1
F5,T5,M5F5=D5*E5
F6:F29,T6:T29,M6:M29F6=(D6*E6)+F5
E40E40=MATCH(CNumber,Number,0)
E41E41=MATCH(CDataSet,DataSet,0)
E42E42=INDEX(Data2,Blue,Pink)
Named Ranges
NameRefers ToCells
Blue=Data!$E$40E42
CDataSet=Data!$F$39E41
CNumber=Data!$E$39E40
Data2=Data!$D$5:$AL$29E42, F5
DataSet=Data!$3:$3E41
Number=Data!$B:$BE40
Pink=Data!$E$41E42


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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top