Help with multi-function (SUM, COUNTIF, AVG) coding and graph

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
331
Happy Wednesday!

My range is A2:C241
In A2:A241 there are categories Australia, Europe, Japan, South Africa, South Korea, Taiwan, Leadership, Administration
In B2:B241 there are 30 different city names for each of the categories in column A
In C2:C241 there are numbers

I would like to count all the numbers in column C that correspond to each category in column A
I would like to count all the numbers in column C for each city represented
I would like to pull the average total number for each city in Column B and then rank all the cities by their total number, then display in a bar graph
I would also like to pull the average number for each category in column A among all cities and also represent in a bar graph

Would anyone be willing to work with me on this? Thank you!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,775
Highlight the data and Insert>Pivot Table (Do a separate one for all your questions)

1615418578031.png


select a location for it

1615418656035.png


Then organise the fields by dragging the headers into one of the four boxes down the bottom

1615418718126.png


1615418750481.png



To change SUM to AVERAE for question 3, select value field settings

1615418798457.png


Average then OK

1615418829653.png


To rank them select the drop down on Row Labels and More Sort Options, make your selection and OK.

1615418903733.png


1615418921528.png


Insert Pivot Chart, select chart type and ok.

1615419013222.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

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
Top