Calculate percentage of selected range/category from a large set of data

BrownEagle143

New Member
Joined
Nov 14, 2018
Messages
2
Hi all, I am new to mrExcel. I have a typical requirement. Currently I have one document which as the inventory of the vehicles. I need to calculate the percentage of the number of vehicles by city in a new column. The sample data as follows
CountryStateCityBrandno of cars
USAillinoisChicagoHyundai550
USAillinoisChicagoHonda1500
USAillinoisChicagoToyota2500
USAillinoisChicagoFord2200
USAillinoisChicagoChevrolet1200
USAillinoisChicagoKia450
USAillinoisAuroraHyundai800
USAillinoisAuroraHonda1200
USAillinoisAuroraToyota1242
USAillinoisAuroraFord2750
USAillinoisRockfordToyota758
USAillinoisRockfordFord350
USAIndianaIndianapolisHyundai550
USAIndianaIndianapolisHonda2458
USAIndianaIndianapolisToyota3685
USAIndianaIndianapolisFord1520
USAIndianaIndianapolisChevrolet2544
USAIndianaIndianapolisKia854
USAIndianaIndianapolisAudi450
USAIndianaIndianapolisBMW358
USAIndianaFort WayneHyundai230
USAIndianaFort WayneHonda1245
USAIndianaFort WayneToyota3551
USAIndianaFort WayneFord784
USAIndianaSouth BendHyundai1255
USAIndianaFort WayneHonda358
USAIndianaFort WayneToyota712

<tbody>
</tbody>


I need to calculate the percentage of vehicles in the next column by city in the following format

CountryStateCityBrandno of carspercentage
USAillinoisChicagoHyundai5506.55
USAillinoisChicagoHonda150017.86
USAillinoisChicagoToyota250029.76
USAillinoisChicagoFord220026.19
USAillinoisChicagoChevrolet120014.29
USAillinoisChicagoKia4505.36
USAillinoisChicago Total8400
USAillinoisAuroraHyundai80013.35
USAillinoisAuroraHonda120020.03
USAillinoisAuroraToyota124220.73
USAillinoisAuroraFord275045.89
USAillinoisAurora Total5992
USAillinoisRockfordToyota75868.41
USAillinoisRockfordFord35031.59
USAillinoisRockford Total1108
USAIndianaIndianapolisHyundai5504.43
USAIndianaIndianapolisHonda245819.79
USAIndianaIndianapolisToyota368529.67
USAIndianaIndianapolisFord152012.24
USAIndianaIndianapolisChevrolet254420.48
USAIndianaIndianapolisKia8546.88
USAIndianaIndianapolisAudi4503.62
USAIndianaIndianapolisBMW3582.88
USAIndianaIndianapolis Total12419
USAIndianaFort WayneHyundai2303.96
USAIndianaFort WayneHonda124521.43
USAIndianaFort WayneToyota355161.12
USAIndianaFort WayneFord78413.49
USAIndianaFort Wayne Total5810
USAIndianaSouth BendHyundai125553.98
USAIndianaSouth BendHonda35815.40
USAIndianaSouth BendToyota71230.62
USAIndianaSouth Bend Total2325

<tbody>
</tbody>


Thanks in Advance.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
=E2/SUMIF($C$2:$C$28,C2,$E$2:$E$28)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,082,131
Messages
5,363,337
Members
400,726
Latest member
Shahzad Taimoor

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top