MINIFS excluding array of values from criteria range

D

DTJ

New Member
Joined
Mar 28, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello to everyone,

I have a problem that I can't solve, and my head is going to explode....o_O

I have a list of slots in column AC and i want to split them in columns AK/AL/AM according to some criteria.
One of those criteria is that a slot can be inserted only one time, so i need to check minimum slot value that has not been already inserted before.
My problem is that i cant insert a criteria to exclude from options AK6 to AM7 slot values (for example AC$6:AC$1337;"<>"&AK6:AM7), so i use criteria as below, but this is not possible to be done for all cells, as i will always need to add more and more criteria.

Any ideas ????


Screenshot_1.png
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Welcome to the MrExcel board!

I'm not sure that I fully understand what you are trying to achieve, but does this help at all?

22 03 29.xlsm
ACAKALAM
5Slot1Slot2Slot3
61123
72456
83789
94101112
105131415
116161718
DTJ
Cell Formulas
RangeFormula
AK6:AM11AK6=INDEX($AC$6:$AC$1337,(ROWS(AK$6:AK6)-1)*3+COLUMNS($AK6:AK6))


BTW, it would help greatly if you provided sample data in a form that helpers can copy to test with. For the future, consider XL2BB
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: MINIFS excluding array of values from criteria range
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Dear Peter, Hope i did it correctly and i can expain better what the logic shoul be

Book1
ACADAEAFAJAKAL
1
2
3
4
5SlotsMarketWeekYearSkid 1 (EU&US)Skid 2 (ONLY EU)Skid 3 (ONLY EU)
61EU120222conditions: minimum number from "AC" that is different from "AJ6" and isn't US (it shoulg give result 1)conditions: next number from "AC" that that is different from "AJ6:AK6" and isn't US (it shoulg give result 5)
72US22022conditions: minimum number from "AC" that is different from "AJ6:AL6" (it shoulg give result 3)conditions: minimum number from "AC" that is different from "AJ6:AL6 & AJ7" and isn't US (it shoulg give result 6)conditions: minimum number from "AC" that is different from "AJ6:AL6 & AJ7:AK7" and isn't US (it shoulg give result 7)
83US32022conditions: minimum number from "AC" that is different from "AJ6:AL7" (it shoulg give result 4) so goes on so goes on
94US42022 so goes on so goes on so goes on
105EU52022 so goes on so goes on so goes on
116EU62022 so goes on so goes on so goes on
127EU72022 so goes on so goes on so goes on
138EU82022 so goes on so goes on so goes on
149EU92022 so goes on so goes on so goes on
1510EU102022 so goes on so goes on so goes on
1611EU112022 so goes on so goes on so goes on
1712EU122022 so goes on so goes on so goes on
1813EU132022 so goes on so goes on so goes on
1914EU142022 so goes on so goes on so goes on
2015EU152022
2116EU162022
2217EU172022
2318EU182022
2419EU192022
2520EU202022
2621EU212022
2722EU222022
2823EU232022
2924EU242022
3025EU252022
3126EU262022
3227EU272022
3328EU282022
3429EU292022
3530EU302022
3631EU312022
3732EU322022
3833EU332022
3934EU342022
4035EU352022
4136EU362022
4237EU372022
4338US382022
4439US392022
4540US402022
4641US412022
4742US422022
4843US432022
4944US442022
5045US452022
5146EU462022
5247EU472022
5348EU482022
5449EU492022
5550EU502022
5651EU512022
5752US522022
5853US12023
5954US22023
6055EU32023
6156EU42023
6257EU52023
6358EU62023
6459EU72023
6560US82023
6661US92023
6762US102023
6863EU112023
6964EU122023
7065EU132023
7166EU142023
7267EU152023
7368EU162023
7469EU172023
7570EU182023
7671EU192023
7772EU202023
7873EU212023
7974EU222023
8075EU232023
8176EU242023
8277EU252023
8378EU262023
8479EU272023
8580EU282023
8681EU292023
8782EU302023
8883EU312023
8984EU322023
9085EU332023
9186EU342023
9287EU352023
9388EU362023
9489EU372023
9590EU382023
9691EU392023
9792EU402023
9893EU412023
9994EU422023
10095EU432023
10196EU442023
10297EU452023
10398EU462023
10499EU472023
105100EU482023
106101US492023
107102US502023
108103US512023
109104US522023
110105US12024
111106US22024
112107US32024
113108US42024
114109EU52024
115110EU62024
116111EU72024
117112EU82024
118113EU92024
119114EU102024
120115US112024
121116US122024
122117US132024
123118EU142024
124119EU152024
125120EU162024
126121EU172024
127122EU182024
128123US192024
129124US202024
130125US212024
131126EU222024
132127EU232024
133128EU242024
134129EU252024
135130EU262024
136131EU272024
137132EU282024
138133EU292024
139134EU302024
140135EU312024
141136EU322024
142137EU332024
143138EU342024
144139EU352024
145140EU362024
146141EU372024
147142EU382024
148143EU392024
149144EU402024
150145EU412024
151146EU422024
152147EU432024
153148EU442024
154149EU452024
155150EU462024
156151EU472024
157152EU482024
158153EU492024
159154EU502024
160155EU512024
161156EU522024
162157EU12025
163158EU22025
164159EU32025
165160EU42025
166161EU52025
167162EU62025
168163EU72025
169164US82025
170165US92025
171166US102025
172167US112025
173168US122025
174169US132025
175170US142025
176171US152025
177172EU162025
178173EU172025
179174EU182025
180175EU192025
181176EU202025
182177EU212025
183178US222025
184179US232025
Sheet1
Cell Formulas
RangeFormula
AJ6AJ6=IF(AD6="us",AC6,IF(AD7="us",AC7,IF(AD8="us",AC8,AC6)))
 
Upvote 0
Fluff said:
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: MINIFS excluding array of values from criteria range
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Hi!

Sorry for this time, I will in next threads!

Thanks!
 
Upvote 0
See if this is it. I was not sure about cell AJ6 so I have just left your formula there.
AK6 gets copied across to AL6.
The row 7 formulas all get copied down as far as you might need.

DTJ.xlsm
ACADAJAKAL
1
2
3
4
5SlotsMarketSkid 1 (EU&US)Skid 2 (ONLY EU)Skid 3 (ONLY EU)
61EU215
72US367
83US489
94US101112
105EU131415
116EU161718
127EU192021
138EU222324
149EU252627
1510EU282930
1611EU313233
1712EU343536
1813EU374647
1914EU384849
2015EU395051
2116EU405556
2217EU415758
2318EU425963
2419EU436465
2520EU446667
2621EU456869
2722EU527071
2823EU537273
2924EU547475
3025EU607677
3126EU617879
3227EU628081
3328EU828384
3429EU858687
3530EU888990
3631EU919293
3732EU949596
3833EU979899
3934EU100109110
4035EU101111112
4136EU102113114
4237EU103118119
4338US104120121
4439US105122126
4540US106127128
4641US107129130
4742US108131132
4843US115133134
4944US116135136
5045US117137138
5146EU123139140
5247EU124141142
5348EU125143144
5449EU145146147
5550EU148149150
5651EU151152153
5752US154155156
5853US157158159
5954US160161162
6055EU163172173
6156EU164174175
6257EU165176177
6358EU166  
6459EU167  
6560US168  
6661US169  
6762US170  
6863EU171  
6964EU178  
7065EU179  
7166EU   
7267EU   
7368EU   
7469EU   
7570EU   
7671EU   
7772EU   
179174EU   
180175EU   
181176EU   
182177EU   
183178US   
184179US   
Sheet1
Cell Formulas
RangeFormula
AJ6AJ6=IF(AD6="us",AC6,IF(AD7="us",AC7,IF(AD8="us",AC8,AC6)))
AK6:AL6AK6=AGGREGATE(15,6,$AC6:$AC184/($AD6:$AD184="EU"),COLUMNS($AK:AK))
AJ7:AJ77,AJ179:AJ184AJ7=IFERROR(AGGREGATE(15,6,AC$6:AC$184/(COUNTIF(AJ$6:AL6,AC$6:AC$184)=0),1),"")
AK7:AK77,AK179:AK184AK7=INDEX(FILTER(AC$6:AC$184,(AD$6:AD$184="EU")*(COUNTIF(AK$6:AL6,AC$6:AC$184)=0)*(COUNTIF(AJ$6:AJ7,AC$6:AC$184)=0),""),1)
AL7:AL77,AL179:AL184AL7=INDEX(FILTER(AC$6:AC$184,(AD$6:AD$184="EU")*(COUNTIF(AL$6:AL6,AC$6:AC$184)=0)*(COUNTIF(AJ$6:AK7,AC$6:AC$184)=0),""),1)
 
Upvote 0
Although I think that the above formulas are working, the sheet is already 'sluggish' for me and I note that your actual data may be considerably larger.
These formulas are a little faster but may still prove problematic if your data is very large.

DTJ.xlsm
ACADAJAKAL
1
2
3
4
5SlotsMarketSkid 1 (EU&US)Skid 2 (ONLY EU)Skid 3 (ONLY EU)
61EU215
72US367
83US489
94US101112
105EU131415
116EU161718
127EU192021
138EU222324
149EU252627
1510EU282930
1611EU313233
1712EU343536
1813EU374647
Sheet2 (2)
Cell Formulas
RangeFormula
AJ6AJ6=IF(AD6="us",AC6,IF(AD7="us",AC7,IF(AD8="us",AC8,AC6)))
AK6:AK18AK6=LET(nums,AC$6:AC$184,IFERROR(AGGREGATE(15,6,nums/((AD$6:AD$184="EU")*(ISNA(MATCH(nums,AJ$5:AJ6,0)))*(ISNA(MATCH(nums,AK$5:AK5,0)))*(ISNA(MATCH(nums,AL$5:AL5,0)))),1),""))
AL6:AL18AL6=LET(nums,AC$6:AC$184,IFERROR(AGGREGATE(15,6,nums/((AD$6:AD$184="EU")*(ISNA(MATCH(nums,AJ$5:AJ6,0)))*(ISNA(MATCH(nums,AK$5:AK6,0)))*(ISNA(MATCH(nums,AL$5:AL5,0)))),1),""))
AJ7:AJ18AJ7=LET(nums,AC$6:AC$184,IFERROR(AGGREGATE(15,6,nums/((ISNA(MATCH(nums,AJ$5:AJ6,0)))*(ISNA(MATCH(nums,AK$5:AK6,0)))*(ISNA(MATCH(nums,AL$5:AL6,0)))),1),""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Excluding a cell from MINIFS calculation - error when excluded cell is equal to first cell in range
Replies
2
Views
274
FormR
FormR
Jeffrey Mahoney
  • Solved
Get Items From Descriptions Based on List
Replies
5
Views
230
Fluff
Fluff
S
  • Question
Autofilter in VBA when criteria is has more words separated by a comma in 2 sheets
Replies
0
Views
490
shafiey
S
R
  • Solved
Result from multiple criteria and range between two values
Replies
3
Views
384
rythmic_flow
R
R
  • Solved
Conditional Formatting - Multiple wildcard criteria
Replies
4
Views
1K
robtyketto
R

Forum statistics

Threads
1,215,641
Messages
6,125,981
Members
449,276
Latest member
surendra75

Share this page

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