Accept Max values

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,132
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
I have this data and have pasted in xl2bb sheet, and the concern is from the data it sould mention as "Do not include" in result coulmn. I tried many ways , like rank, match index and lot more dut non wokred.

idea is from very region, place type, isp name and platform it should only take the max number from sample_count and sound display Do not include for the low sample_count, not sure if the macor or a esxcel function will work.

any help willbe great. Ihave tried to explain it in the best way, it will be great to fig it out.

aggregate_dateplace_nameregionplace_typeisp_nameaggregation_periodplatformtest_countsample_countResult
1-Aug-23​
Saudi ArabiacountrystcMonthAll Fixed
10​
7​
Do not include
1-Aug-23​
Saudi ArabiacountrystcMonthAll Fixed
2​
2​
Do not include
1-Aug-23​
Saudi ArabiacountrystcMonthAll Fixed
1033229​
288884​

aggregate_dateplace_nameregionplace_typeisp_nameaggregation_periodplatformtest_countsample_countResult
1-Aug-23​
Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomMonthAll Fixed
946​
270​
1-Aug-23​
Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomMonthAll Fixed
906​
257​
Do not include


Test.xlsx
A
1aggregate_date
Sheet1
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
here you go,

Test.xlsx
ABCDEFGHIJ
1aggregate_dateplace_nameregionplace_typeisp_nameaggregation_periodplatformtest_countsample_countResult
21-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1GO TelecomMonthAll Fixed5123
31-Aug-23Saudi ArabiacountryGO TelecomMonthFiber7468
41-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1SalamMonthFiber2719
51-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1GO TelecomMonthFiber44
61-Aug-23Saudi ArabiacountrystcMonthAll Fixed107Do not include
71-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthFiber22
81-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthAll Fixed107Do not include
91-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthAll Fixed22Do not include
101-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1SalamMonthFiber5313
111-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1GO TelecomMonthFiber21
121-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomMonthFiber4742
131-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1SalamMonthFiber21
141-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1GO TelecomMonthFiber1515
151-Aug-23Najran ProvinceNajran Provinceadministrative_area_level_1GO TelecomMonthAll Fixed8214
161-Aug-23Saudi ArabiacountrystcMonthAll Fixed22Do not include
171-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomMonthFiber33
181-Aug-23Najran ProvinceNajran Provinceadministrative_area_level_1MobilyMonthAll Fixed7230
191-Aug-23Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1SalamMonthAll Fixed86
201-Aug-23Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed2310
211-Aug-23Jazan ProvinceJazan Provinceadministrative_area_level_1SalamMonthAll Fixed4122
221-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1GO TelecomMonthAll Fixed2406776
231-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1SalamMonthAll Fixed347129
241-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1GO TelecomMonthAll Fixed261132
251-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1GO TelecomMonthAll Fixed25391
261-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1SalamMonthAll Fixed582216
271-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomMonthAll Fixed946270
281-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1GO TelecomMonthAll Fixed13124
291-Aug-23Saudi ArabiacountryGO TelecomMonthAll Fixed195345800
301-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1GO TelecomMonthAll Fixed1810
311-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed37721284
321-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1SalamMonthFiber583152
331-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomMonthAll Fixed85712950
341-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1SalamMonthAll Fixed96973384
351-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1MobilyMonthAll Fixed15351
361-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1SalamMonthAll Fixed260307841
371-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1SalamMonthFiber218776574
381-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1ZainMonthAll Fixed6798913305
391-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1SalamMonthAll Fixed1063319
401-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1SalamMonthFiber13246
411-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1ZainMonthAll Fixed3514847
421-Aug-23Najran ProvinceNajran Provinceadministrative_area_level_1SalamMonthAll Fixed23588
431-Aug-23Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1MobilyMonthAll Fixed21054
441-Aug-23Saudi ArabiacountrySalamMonthAll Fixed9877031859
451-Aug-23Saudi ArabiacountrySalamMonthFiber280198669
461-Aug-23Jazan ProvinceJazan Provinceadministrative_area_level_1GO TelecomMonthAll Fixed1357167
471-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1ZainMonthAll Fixed45701109
481-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1stcMonthAll Fixed3159510107
491-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1SalamMonthFiber14651
501-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1SalamMonthAll Fixed32183
511-Aug-23Saudi ArabiacountrystcMonthAll Fixed1033229288884
521-Aug-23Saudi ArabiacountrystcMonthFiber664190204975
531-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1ZainMonthAll Fixed2056439
541-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1stcMonthAll Fixed50041439
551-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1stcMonthFiber3010778
561-Aug-23Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1MobilyMonthAll Fixed480173
571-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1stcMonthFiber249207763
581-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1MobilyMonthAll Fixed1629577
591-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1stcMonthAll Fixed148184136
601-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1ZainMonthAll Fixed82131873
611-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1stcMonthAll Fixed2884110533
621-Aug-23Saudi ArabiacountryZainMonthAll Fixed31328471872
631-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1stcMonthAll Fixed40921247
641-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1stcMonthFiber2903831
651-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1MobilyMonthAll Fixed332109
661-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthAll Fixed17282151776
671-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthFiber12639238026
681-Aug-23Eastern ProvinceEastern Provinceadministrative_area_level_1MobilyMonthAll Fixed3830811061
691-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1stcMonthFiber86532565
701-Aug-23Tabuk ProvinceTabuk Provinceadministrative_area_level_1MobilyMonthAll Fixed2504727
711-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1stcMonthFiber105223761
721-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1MobilyMonthAll Fixed2219740
731-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1SalamMonthAll Fixed41396
741-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1SalamMonthFiber32183
751-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1ZainMonthAll Fixed1761386
761-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1stcMonthAll Fixed28659885591
771-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1stcMonthFiber16899853432
781-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1MobilyMonthAll Fixed8777025657
791-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1SalamMonthAll Fixed2487780
801-Aug-23Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1ZainMonthAll Fixed1005380
811-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed1276315
821-Aug-23Najran ProvinceNajran Provinceadministrative_area_level_1ZainMonthAll Fixed890196
831-Aug-23Najran ProvinceNajran Provinceadministrative_area_level_1stcMonthAll Fixed76583032
841-Aug-23Najran ProvinceNajran Provinceadministrative_area_level_1stcMonthFiber2647859
851-Aug-23Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1ZainMonthAll Fixed851216
861-Aug-23Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1stcMonthAll Fixed48201519
871-Aug-23Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1stcMonthFiber1826600
881-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1ZainMonthAll Fixed10276122730
891-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1stcMonthAll Fixed79312239
901-Aug-23Hail ProvinceHail Provinceadministrative_area_level_1stcMonthFiber52331368
911-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1SalamMonthAll Fixed52251674
921-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1ZainMonthAll Fixed149054223
931-Aug-23Saudi ArabiacountryMobilyMonthAll Fixed18747255819
941-Aug-23Jazan ProvinceJazan Provinceadministrative_area_level_1ZainMonthAll Fixed54081161
951-Aug-23Jazan ProvinceJazan Provinceadministrative_area_level_1stcMonthAll Fixed400999178
961-Aug-23Jazan ProvinceJazan Provinceadministrative_area_level_1stcMonthFiber156773723
971-Aug-23Jazan ProvinceJazan Provinceadministrative_area_level_1MobilyMonthAll Fixed1682448
981-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1SalamMonthFiber48781745
991-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1stcMonthAll Fixed36057596622
1001-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1stcMonthFiber24739077644
1011-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1MobilyMonthAll Fixed4919515356
1021-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1stcMonthAll Fixed6047819449
1031-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1stcMonthFiber4594515834
1041-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1MobilyMonthAll Fixed26441067
1051-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1SalamMonthAll Fixed5182217371
1061-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1ZainMonthAll Fixed9921926905
1071-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthFiber22
1081-Aug-23Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomMonthFiber4338
1091-Aug-23Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomMonthFiber33
1101-Aug-23Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed36371242Do not include
1111-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1SalamMonthAll Fixed555209Do not include
1121-Aug-23Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomMonthAll Fixed906257Do not include
1131-Aug-23Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1GO TelecomMonthAll Fixed179Do not include
Sheet1
 
Upvote 0
I have tried this code, but its not working as required

any other way for it.


VBA Code:
sub text()
Dim zelle As Range
Dim rng As Range
Dim lCol As Long
Dim lLastRow As Long
  With ActiveSheet
    For lCol = 2 To .UsedRange.Columns.Count
      lLastRow = .Cells(.Rows.Count, lCol).End(xlUp).Row
      Set rng = .Range(.Cells(2, lCol), .Cells(lLastRow, lCol))
      For Each zelle In rng
        If zelle.Value = Application.WorksheetFunction.Max(rng) Then
          zelle.Interior.ColorIndex = 6
        Else
          zelle.Interior.ColorIndex = xlNone
        End If
      Next
    Next lCol
  End With
End Sub
 
Upvote 0
Hope this helps

Book3
ABCDEFGHIJK
1aggregate_dateplace_nameregionplace_typeisp_nameaggregation_periodplatformtest_countsample_countResultIdentifier
245139Hail ProvinceHail Provinceadministrative_area_level_1GO TelecomMonthAll Fixed5123 Hail ProvinceHail Provinceadministrative_area_level_1GO TelecomAll Fixed
345139Saudi ArabiacountryGO TelecomMonthFiber7468 Saudi ArabiacountryGO TelecomFiber
445139Tabuk ProvinceTabuk Provinceadministrative_area_level_1SalamMonthFiber2719 Tabuk ProvinceTabuk Provinceadministrative_area_level_1SalamFiber
545139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1GO TelecomMonthFiber44 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1GO TelecomFiber
645139Saudi ArabiacountrystcMonthAll Fixed107Do not includeSaudi ArabiacountrystcAll Fixed
745139Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthFiber22 Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomFiber
845139Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthAll Fixed107Do not includeEastern ProvinceEastern Provinceadministrative_area_level_1stcAll Fixed
945139Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthAll Fixed22Do not includeEastern ProvinceEastern Provinceadministrative_area_level_1stcAll Fixed
1045139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1SalamMonthFiber5313 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1SalamFiber
1145139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1GO TelecomMonthFiber21 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1GO TelecomFiber
1245139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomMonthFiber4742 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomFiber
1345139Makkah ProvinceMakkah Provinceadministrative_area_level_1SalamMonthFiber21 Makkah ProvinceMakkah Provinceadministrative_area_level_1SalamFiber
1445139Eastern ProvinceEastern Provinceadministrative_area_level_1GO TelecomMonthFiber1515 Eastern ProvinceEastern Provinceadministrative_area_level_1GO TelecomFiber
1545139Najran ProvinceNajran Provinceadministrative_area_level_1GO TelecomMonthAll Fixed8214 Najran ProvinceNajran Provinceadministrative_area_level_1GO TelecomAll Fixed
1645139Saudi ArabiacountrystcMonthAll Fixed22Do not includeSaudi ArabiacountrystcAll Fixed
1745139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomMonthFiber33 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomFiber
1845139Najran ProvinceNajran Provinceadministrative_area_level_1MobilyMonthAll Fixed7230 Najran ProvinceNajran Provinceadministrative_area_level_1MobilyAll Fixed
1945139Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1SalamMonthAll Fixed86 Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1SalamAll Fixed
2045139Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed2310 Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1GO TelecomAll Fixed
2145139Jazan ProvinceJazan Provinceadministrative_area_level_1SalamMonthAll Fixed4122 Jazan ProvinceJazan Provinceadministrative_area_level_1SalamAll Fixed
2245139Eastern ProvinceEastern Provinceadministrative_area_level_1GO TelecomMonthAll Fixed2406776 Eastern ProvinceEastern Provinceadministrative_area_level_1GO TelecomAll Fixed
2345139Tabuk ProvinceTabuk Provinceadministrative_area_level_1SalamMonthAll Fixed347129 Tabuk ProvinceTabuk Provinceadministrative_area_level_1SalamAll Fixed
2445139Tabuk ProvinceTabuk Provinceadministrative_area_level_1GO TelecomMonthAll Fixed261132 Tabuk ProvinceTabuk Provinceadministrative_area_level_1GO TelecomAll Fixed
2545139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1GO TelecomMonthAll Fixed25391 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1GO TelecomAll Fixed
2645139Aseer ProvinceAseer Provinceadministrative_area_level_1SalamMonthAll Fixed582216 Aseer ProvinceAseer Provinceadministrative_area_level_1SalamAll Fixed
2745139Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomMonthAll Fixed946270 Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomAll Fixed
2845139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1GO TelecomMonthAll Fixed13124 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1GO TelecomAll Fixed
2945139Saudi ArabiacountryGO TelecomMonthAll Fixed195345800 Saudi ArabiacountryGO TelecomAll Fixed
3045139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1GO TelecomMonthAll Fixed1810 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1GO TelecomAll Fixed
3145139Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed37721284 Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomAll Fixed
3245139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1SalamMonthFiber583152 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1SalamFiber
3345139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomMonthAll Fixed85712950 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomAll Fixed
3445139Makkah ProvinceMakkah Provinceadministrative_area_level_1SalamMonthAll Fixed96973384 Makkah ProvinceMakkah Provinceadministrative_area_level_1SalamAll Fixed
3545139Hail ProvinceHail Provinceadministrative_area_level_1MobilyMonthAll Fixed15351 Hail ProvinceHail Provinceadministrative_area_level_1MobilyAll Fixed
3645139Eastern ProvinceEastern Provinceadministrative_area_level_1SalamMonthAll Fixed260307841 Eastern ProvinceEastern Provinceadministrative_area_level_1SalamAll Fixed
3745139Eastern ProvinceEastern Provinceadministrative_area_level_1SalamMonthFiber218776574 Eastern ProvinceEastern Provinceadministrative_area_level_1SalamFiber
3845139Eastern ProvinceEastern Provinceadministrative_area_level_1ZainMonthAll Fixed6798913305 Eastern ProvinceEastern Provinceadministrative_area_level_1ZainAll Fixed
3945139Hail ProvinceHail Provinceadministrative_area_level_1SalamMonthAll Fixed1063319 Hail ProvinceHail Provinceadministrative_area_level_1SalamAll Fixed
4045139Hail ProvinceHail Provinceadministrative_area_level_1SalamMonthFiber13246 Hail ProvinceHail Provinceadministrative_area_level_1SalamFiber
4145139Hail ProvinceHail Provinceadministrative_area_level_1ZainMonthAll Fixed3514847 Hail ProvinceHail Provinceadministrative_area_level_1ZainAll Fixed
4245139Najran ProvinceNajran Provinceadministrative_area_level_1SalamMonthAll Fixed23588 Najran ProvinceNajran Provinceadministrative_area_level_1SalamAll Fixed
4345139Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1MobilyMonthAll Fixed21054 Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1MobilyAll Fixed
4445139Saudi ArabiacountrySalamMonthAll Fixed9877031859 Saudi ArabiacountrySalamAll Fixed
4545139Saudi ArabiacountrySalamMonthFiber280198669 Saudi ArabiacountrySalamFiber
4645139Jazan ProvinceJazan Provinceadministrative_area_level_1GO TelecomMonthAll Fixed1357167 Jazan ProvinceJazan Provinceadministrative_area_level_1GO TelecomAll Fixed
4745139Tabuk ProvinceTabuk Provinceadministrative_area_level_1ZainMonthAll Fixed45701109 Tabuk ProvinceTabuk Provinceadministrative_area_level_1ZainAll Fixed
4845139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1stcMonthAll Fixed3159510107 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1stcAll Fixed
4945139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1SalamMonthFiber14651 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1SalamFiber
5045139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1SalamMonthAll Fixed32183 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1SalamAll Fixed
5145139Saudi ArabiacountrystcMonthAll Fixed1033229288884 Saudi ArabiacountrystcAll Fixed
5245139Saudi ArabiacountrystcMonthFiber664190204975 Saudi ArabiacountrystcFiber
5345139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1ZainMonthAll Fixed2056439 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1ZainAll Fixed
5445139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1stcMonthAll Fixed50041439 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1stcAll Fixed
5545139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1stcMonthFiber3010778 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1stcFiber
5645139Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1MobilyMonthAll Fixed480173 Al Jowf ProvinceAl Jowf Provinceadministrative_area_level_1MobilyAll Fixed
5745139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1stcMonthFiber249207763 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1stcFiber
5845139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1MobilyMonthAll Fixed1629577 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1MobilyAll Fixed
5945139Tabuk ProvinceTabuk Provinceadministrative_area_level_1stcMonthAll Fixed148184136 Tabuk ProvinceTabuk Provinceadministrative_area_level_1stcAll Fixed
6045139Aseer ProvinceAseer Provinceadministrative_area_level_1ZainMonthAll Fixed82131873 Aseer ProvinceAseer Provinceadministrative_area_level_1ZainAll Fixed
6145139Aseer ProvinceAseer Provinceadministrative_area_level_1stcMonthAll Fixed2884110533 Aseer ProvinceAseer Provinceadministrative_area_level_1stcAll Fixed
6245139Saudi ArabiacountryZainMonthAll Fixed31328471872 Saudi ArabiacountryZainAll Fixed
6345139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1stcMonthAll Fixed40921247 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1stcAll Fixed
6445139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1stcMonthFiber2903831 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1stcFiber
6545139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1MobilyMonthAll Fixed332109 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1MobilyAll Fixed
6645139Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthAll Fixed17282151776 Eastern ProvinceEastern Provinceadministrative_area_level_1stcAll Fixed
6745139Eastern ProvinceEastern Provinceadministrative_area_level_1stcMonthFiber12639238026 Eastern ProvinceEastern Provinceadministrative_area_level_1stcFiber
6845139Eastern ProvinceEastern Provinceadministrative_area_level_1MobilyMonthAll Fixed3830811061 Eastern ProvinceEastern Provinceadministrative_area_level_1MobilyAll Fixed
6945139Tabuk ProvinceTabuk Provinceadministrative_area_level_1stcMonthFiber86532565 Tabuk ProvinceTabuk Provinceadministrative_area_level_1stcFiber
7045139Tabuk ProvinceTabuk Provinceadministrative_area_level_1MobilyMonthAll Fixed2504727 Tabuk ProvinceTabuk Provinceadministrative_area_level_1MobilyAll Fixed
7145139Aseer ProvinceAseer Provinceadministrative_area_level_1stcMonthFiber105223761 Aseer ProvinceAseer Provinceadministrative_area_level_1stcFiber
7245139Aseer ProvinceAseer Provinceadministrative_area_level_1MobilyMonthAll Fixed2219740 Aseer ProvinceAseer Provinceadministrative_area_level_1MobilyAll Fixed
7345139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1SalamMonthAll Fixed41396 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1SalamAll Fixed
7445139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1SalamMonthFiber32183 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1SalamFiber
7545139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1ZainMonthAll Fixed1761386 Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1ZainAll Fixed
7645139Makkah ProvinceMakkah Provinceadministrative_area_level_1stcMonthAll Fixed28659885591 Makkah ProvinceMakkah Provinceadministrative_area_level_1stcAll Fixed
7745139Makkah ProvinceMakkah Provinceadministrative_area_level_1stcMonthFiber16899853432 Makkah ProvinceMakkah Provinceadministrative_area_level_1stcFiber
7845139Makkah ProvinceMakkah Provinceadministrative_area_level_1MobilyMonthAll Fixed8777025657 Makkah ProvinceMakkah Provinceadministrative_area_level_1MobilyAll Fixed
7945139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1SalamMonthAll Fixed2487780 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1SalamAll Fixed
8045139Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1ZainMonthAll Fixed1005380 Al Qassim ProvinceAl Qassim Provinceadministrative_area_level_1ZainAll Fixed
8145139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed1276315 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomAll Fixed
8245139Najran ProvinceNajran Provinceadministrative_area_level_1ZainMonthAll Fixed890196 Najran ProvinceNajran Provinceadministrative_area_level_1ZainAll Fixed
8345139Najran ProvinceNajran Provinceadministrative_area_level_1stcMonthAll Fixed76583032 Najran ProvinceNajran Provinceadministrative_area_level_1stcAll Fixed
8445139Najran ProvinceNajran Provinceadministrative_area_level_1stcMonthFiber2647859 Najran ProvinceNajran Provinceadministrative_area_level_1stcFiber
8545139Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1ZainMonthAll Fixed851216 Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1ZainAll Fixed
8645139Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1stcMonthAll Fixed48201519 Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1stcAll Fixed
8745139Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1stcMonthFiber1826600 Al Bahah ProvinceAl Bahah Provinceadministrative_area_level_1stcFiber
8845139Makkah ProvinceMakkah Provinceadministrative_area_level_1ZainMonthAll Fixed10276122730 Makkah ProvinceMakkah Provinceadministrative_area_level_1ZainAll Fixed
8945139Hail ProvinceHail Provinceadministrative_area_level_1stcMonthAll Fixed79312239 Hail ProvinceHail Provinceadministrative_area_level_1stcAll Fixed
9045139Hail ProvinceHail Provinceadministrative_area_level_1stcMonthFiber52331368 Hail ProvinceHail Provinceadministrative_area_level_1stcFiber
9145139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1SalamMonthAll Fixed52251674 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1SalamAll Fixed
9245139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1ZainMonthAll Fixed149054223 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1ZainAll Fixed
9345139Saudi ArabiacountryMobilyMonthAll Fixed18747255819 Saudi ArabiacountryMobilyAll Fixed
9445139Jazan ProvinceJazan Provinceadministrative_area_level_1ZainMonthAll Fixed54081161 Jazan ProvinceJazan Provinceadministrative_area_level_1ZainAll Fixed
9545139Jazan ProvinceJazan Provinceadministrative_area_level_1stcMonthAll Fixed400999178 Jazan ProvinceJazan Provinceadministrative_area_level_1stcAll Fixed
9645139Jazan ProvinceJazan Provinceadministrative_area_level_1stcMonthFiber156773723 Jazan ProvinceJazan Provinceadministrative_area_level_1stcFiber
9745139Jazan ProvinceJazan Provinceadministrative_area_level_1MobilyMonthAll Fixed1682448 Jazan ProvinceJazan Provinceadministrative_area_level_1MobilyAll Fixed
9845139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1SalamMonthFiber48781745 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1SalamFiber
9945139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1stcMonthAll Fixed36057596622 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1stcAll Fixed
10045139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1stcMonthFiber24739077644 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1stcFiber
10145139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1MobilyMonthAll Fixed4919515356 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1MobilyAll Fixed
10245139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1stcMonthAll Fixed6047819449 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1stcAll Fixed
10345139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1stcMonthFiber4594515834 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1stcFiber
10445139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1MobilyMonthAll Fixed26441067 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1MobilyAll Fixed
10545139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1SalamMonthAll Fixed5182217371 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1SalamAll Fixed
10645139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1ZainMonthAll Fixed9921926905 Riyadh ProvinceRiyadh Provinceadministrative_area_level_1ZainAll Fixed
10745139Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthFiber22 Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomFiber
10845139Riyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomMonthFiber4338Do not includeRiyadh ProvinceRiyadh Provinceadministrative_area_level_1GO TelecomFiber
10945139Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomMonthFiber33 Al Madinah ProvinceAl Madinah Provinceadministrative_area_level_1GO TelecomFiber
11045139Makkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomMonthAll Fixed36371242Do not includeMakkah ProvinceMakkah Provinceadministrative_area_level_1GO TelecomAll Fixed
11145139Aseer ProvinceAseer Provinceadministrative_area_level_1SalamMonthAll Fixed555209Do not includeAseer ProvinceAseer Provinceadministrative_area_level_1SalamAll Fixed
11245139Aseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomMonthAll Fixed906257Do not includeAseer ProvinceAseer Provinceadministrative_area_level_1GO TelecomAll Fixed
11345139Northern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1GO TelecomMonthAll Fixed179Do not includeNorthern Borders ProvinceNorthern Borders Provinceadministrative_area_level_1GO TelecomAll Fixed
Sheet1
Cell Formulas
RangeFormula
J2:J113J2=IF(MAXIFS($H$2:$H$113,$K$2:$K$113,K2)=H2,"","Do not include")
K2:K113K2=B2&C2&D2&E2&G2
 
Upvote 0
Solution
Hope this helps
Not sure that it will since the OP's profile shows Excel 2013 which does not have MAXIFS.
However, here is another option but using functions for earlier versions and excluding the helper column.

vmjan02.xlsm
CDEGIJ
1regionplace_typeisp_nameplatformsample_countResult
2Hail Provinceadministrative_area_level_1GO TelecomAll Fixed23 
3countryGO TelecomFiber68 
4Tabuk Provinceadministrative_area_level_1SalamFiber19 
5Al Qassim Provinceadministrative_area_level_1GO TelecomFiber4 
6countrystcAll Fixed7Do not include
7Makkah Provinceadministrative_area_level_1GO TelecomFiber2 
8Eastern Provinceadministrative_area_level_1stcAll Fixed7Do not include
9Eastern Provinceadministrative_area_level_1stcAll Fixed2Do not include
10Al Jowf Provinceadministrative_area_level_1SalamFiber13 
11Al Jowf Provinceadministrative_area_level_1GO TelecomFiber1 
12Riyadh Provinceadministrative_area_level_1GO TelecomFiber42 
13Makkah Provinceadministrative_area_level_1SalamFiber1 
14Eastern Provinceadministrative_area_level_1GO TelecomFiber15 
15Najran Provinceadministrative_area_level_1GO TelecomAll Fixed14 
16countrystcAll Fixed2Do not include
17Al Madinah Provinceadministrative_area_level_1GO TelecomFiber3 
18Najran Provinceadministrative_area_level_1MobilyAll Fixed30 
19Al Bahah Provinceadministrative_area_level_1SalamAll Fixed6 
20Al Bahah Provinceadministrative_area_level_1GO TelecomAll Fixed10 
21Jazan Provinceadministrative_area_level_1SalamAll Fixed22 
22Eastern Provinceadministrative_area_level_1GO TelecomAll Fixed776 
23Tabuk Provinceadministrative_area_level_1SalamAll Fixed129 
24Tabuk Provinceadministrative_area_level_1GO TelecomAll Fixed132 
25Al Qassim Provinceadministrative_area_level_1GO TelecomAll Fixed91 
26Aseer Provinceadministrative_area_level_1SalamAll Fixed216 
27Aseer Provinceadministrative_area_level_1GO TelecomAll Fixed270 
28Al Jowf Provinceadministrative_area_level_1GO TelecomAll Fixed24 
29countryGO TelecomAll Fixed5800 
30Northern Borders Provinceadministrative_area_level_1GO TelecomAll Fixed10 
31Makkah Provinceadministrative_area_level_1GO TelecomAll Fixed1284 
32Al Qassim Provinceadministrative_area_level_1SalamFiber152 
33Riyadh Provinceadministrative_area_level_1GO TelecomAll Fixed2950 
34Makkah Provinceadministrative_area_level_1SalamAll Fixed3384 
35Hail Provinceadministrative_area_level_1MobilyAll Fixed51 
36Eastern Provinceadministrative_area_level_1SalamAll Fixed7841 
37Eastern Provinceadministrative_area_level_1SalamFiber6574 
38Eastern Provinceadministrative_area_level_1ZainAll Fixed13305 
39Hail Provinceadministrative_area_level_1SalamAll Fixed319 
40Hail Provinceadministrative_area_level_1SalamFiber46 
41Hail Provinceadministrative_area_level_1ZainAll Fixed847 
42Najran Provinceadministrative_area_level_1SalamAll Fixed88 
43Al Bahah Provinceadministrative_area_level_1MobilyAll Fixed54 
44countrySalamAll Fixed31859 
45countrySalamFiber8669 
46Jazan Provinceadministrative_area_level_1GO TelecomAll Fixed167 
47Tabuk Provinceadministrative_area_level_1ZainAll Fixed1109 
48Al Qassim Provinceadministrative_area_level_1stcAll Fixed10107 
49Al Madinah Provinceadministrative_area_level_1SalamFiber51 
50Al Jowf Provinceadministrative_area_level_1SalamAll Fixed83 
51countrystcAll Fixed288884 
52countrystcFiber204975 
53Al Jowf Provinceadministrative_area_level_1ZainAll Fixed439 
54Al Jowf Provinceadministrative_area_level_1stcAll Fixed1439 
55Al Jowf Provinceadministrative_area_level_1stcFiber778 
56Al Jowf Provinceadministrative_area_level_1MobilyAll Fixed173 
57Al Qassim Provinceadministrative_area_level_1stcFiber7763 
58Al Qassim Provinceadministrative_area_level_1MobilyAll Fixed577 
59Tabuk Provinceadministrative_area_level_1stcAll Fixed4136 
60Aseer Provinceadministrative_area_level_1ZainAll Fixed1873 
61Aseer Provinceadministrative_area_level_1stcAll Fixed10533 
62countryZainAll Fixed71872 
63Northern Borders Provinceadministrative_area_level_1stcAll Fixed1247 
64Northern Borders Provinceadministrative_area_level_1stcFiber831 
65Northern Borders Provinceadministrative_area_level_1MobilyAll Fixed109 
66Eastern Provinceadministrative_area_level_1stcAll Fixed51776 
67Eastern Provinceadministrative_area_level_1stcFiber38026 
68Eastern Provinceadministrative_area_level_1MobilyAll Fixed11061 
69Tabuk Provinceadministrative_area_level_1stcFiber2565 
70Tabuk Provinceadministrative_area_level_1MobilyAll Fixed727 
71Aseer Provinceadministrative_area_level_1stcFiber3761 
72Aseer Provinceadministrative_area_level_1MobilyAll Fixed740 
73Northern Borders Provinceadministrative_area_level_1SalamAll Fixed96 
74Northern Borders Provinceadministrative_area_level_1SalamFiber83 
75Northern Borders Provinceadministrative_area_level_1ZainAll Fixed386 
76Makkah Provinceadministrative_area_level_1stcAll Fixed85591 
77Makkah Provinceadministrative_area_level_1stcFiber53432 
78Makkah Provinceadministrative_area_level_1MobilyAll Fixed25657 
79Al Qassim Provinceadministrative_area_level_1SalamAll Fixed780 
80Al Qassim Provinceadministrative_area_level_1ZainAll Fixed380 
81Al Madinah Provinceadministrative_area_level_1GO TelecomAll Fixed315 
82Najran Provinceadministrative_area_level_1ZainAll Fixed196 
83Najran Provinceadministrative_area_level_1stcAll Fixed3032 
84Najran Provinceadministrative_area_level_1stcFiber859 
85Al Bahah Provinceadministrative_area_level_1ZainAll Fixed216 
86Al Bahah Provinceadministrative_area_level_1stcAll Fixed1519 
87Al Bahah Provinceadministrative_area_level_1stcFiber600 
88Makkah Provinceadministrative_area_level_1ZainAll Fixed22730 
89Hail Provinceadministrative_area_level_1stcAll Fixed2239 
90Hail Provinceadministrative_area_level_1stcFiber1368 
91Al Madinah Provinceadministrative_area_level_1SalamAll Fixed1674 
92Al Madinah Provinceadministrative_area_level_1ZainAll Fixed4223 
93countryMobilyAll Fixed55819 
94Jazan Provinceadministrative_area_level_1ZainAll Fixed1161 
95Jazan Provinceadministrative_area_level_1stcAll Fixed9178 
96Jazan Provinceadministrative_area_level_1stcFiber3723 
97Jazan Provinceadministrative_area_level_1MobilyAll Fixed448 
98Riyadh Provinceadministrative_area_level_1SalamFiber1745 
99Riyadh Provinceadministrative_area_level_1stcAll Fixed96622 
100Riyadh Provinceadministrative_area_level_1stcFiber77644 
101Riyadh Provinceadministrative_area_level_1MobilyAll Fixed15356 
102Al Madinah Provinceadministrative_area_level_1stcAll Fixed19449 
103Al Madinah Provinceadministrative_area_level_1stcFiber15834 
104Al Madinah Provinceadministrative_area_level_1MobilyAll Fixed1067 
105Riyadh Provinceadministrative_area_level_1SalamAll Fixed17371 
106Riyadh Provinceadministrative_area_level_1ZainAll Fixed26905 
107Makkah Provinceadministrative_area_level_1GO TelecomFiber2 
108Riyadh Provinceadministrative_area_level_1GO TelecomFiber38Do not include
109Al Madinah Provinceadministrative_area_level_1GO TelecomFiber3 
110Makkah Provinceadministrative_area_level_1GO TelecomAll Fixed1242Do not include
111Aseer Provinceadministrative_area_level_1SalamAll Fixed209Do not include
112Aseer Provinceadministrative_area_level_1GO TelecomAll Fixed257Do not include
113Northern Borders Provinceadministrative_area_level_1GO TelecomAll Fixed9Do not include
Sheet1
Cell Formulas
RangeFormula
J2:J113J2=IF(I2=AGGREGATE(14,6,I$2:I$113/((C$2:C$113=C2)*(D$2:D$113=D2)*(E$2:E$113=E2)*(G$2:G$113=G2)),1),"","Do not include")
 
Upvote 0
please delete my post as I can no longer edit it.
No need to delete it as it may be possible that the OP has updated their version but not their profile, or in any case other/future readers with MAXIFS may find it useful.
 
Upvote 0
thank you very very much both of you, then function is working all perfect...
peter_ss, yes you are right we have updated all our version now. so maxifs is also working. thank you for your guidence.

armstrongn14, your are perfecrt as well, thank you for the suppor and help and also helping me to understnad as how the maxifs works.

my only one concern, by any changes, as number of rows or record my change so can we have a fix for it.


will this work and there will npot be any problem in futrue.
Excel Formula:
=IF(I2=AGGREGATE(14,6,I$2:I8000/((C$2:C8000=C2)*(D$2:D8000=D2)*(E$2:E8000=E2)*(G$2:G8000=G2)),1),"","Do not include")
 
Upvote 0
we have updated all our version now. so maxifs is also working.
So, should you be updating your account details?

1696668726011.png


will this work and there will npot be any problem in futrue.
Try it?
 
Upvote 0

Similar threads

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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