Counting number of instances of different scores in a filtered list

I need to produce some graphs showing the distribution of scores for different groups.  
There are about 2000 people.  Each person is scored on a scale 1 to 6.  There are about 10 different groups.  
I want to produce a graph for each group showing the distribution of scores in percentages.

If I use the COUNTIF function it counts all the items, whether filtered or not, so this doesn't work.  At the moment the only workaround I can think of is to sort the list by group then copy the data for each group to a different worksheet.  

Any suggestions how I can do this more elegantly please?
I have Excel 2010
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you attach a workbook please?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

data analysis like this can typically be done with pivot tables and charts. The data needs to be in a contiguous table. For your example you would need a column for Group, Person and Score.

Pivot tables and charts can be built with just a few clicks and filtered with slicers or drop-downs in the chart or pivot table. In many scenarios no formulas are required.

See attached for an example.

cheers, teylyn
colin_thamesAuthor Commented:
Thanks Guys
Teylyn, I wondered if someone would suggest pivot tables but it's new to me and I have to get a solution tonight so not much time to learn.  I've removed names and changed class names but this is the structure.  I want to create two graphs.
Martin, I attach an example.
I'm aiming to create two graphs, one for Effort and the other for Knowledge, that will update each time I filter by another class.  Is that possible?
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I suggest two things:

1. You shouldn't "filter" unless you have to. You are right, COUNTIF doesn't use filters, and I don't think that SUBTOTAL which does is what you want. Therefore, I would suggest having the class number that you want to focus on in a cell on it's own, and therefore the formulas (and the graph titles) can use that cell.

2. Instead of using COUNTIF, use COUNTIFS. Almost exactly the same, but you can filter by multiple criteria, e.g.

COUNTIFS(B:B, "A", A:A, "Orange")
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And you can still set up a dropdown list for the class selection cell, so you don't have to type it in and run the risk of misspelling,
colin_thamesAuthor Commented:
Really sorry, Phillip, I'm not sure if I understand what you mean by "having the class number that you want to focus on in a cell on it's own"

Could you show what you mean on the example sheet?
byundtMechanical EngineerCommented:
I urge you to follow teylyn's suggestion of using a PivotTable & PivotChart. Doing so really is quite simple, and you'll be kicking yourself for avoiding it once you learn how.

1. Remove AutoFilter by clicking Data...Filter menu item
2. Create a Table by selecting cell A1 and then using Insert...Table menu item. Click "OK" to close the "Create Table" dialog.
3. Select cell A1 and then Create a PivotTable using the Insert...PivotTable menu item
4. In the first step of the PivotTable wizard, choose to put the PivotTable on the same worksheet, say in cell G1. Click "OK" to close the dialog.
5. You will now be presented with a PivotTable Fields dialog. Drag Class into the Filters box, Knowledge into the Columns box, and Knowledge into the Values box.
6. In cell H1 to the right of the Class label in the PivotTable, choose one of the class colors then click "OK". Note how your counts change.
7. Select any cell in the PivotTable, then go to the Analyze menu that now appears and choose PivotChart. A PivotChart will now appear on the worksheet when you click "OK".

You can filter the data either with the PivotTable or the PivotChart and the histogram will automatically update.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colin_thamesAuthor Commented:
Many thanks guys, especially byundt for the worked example of the pivot table.  I baulked at trying it initially but when I eventually plucked up the courage it worked pretty much as you said.  There wasn't any sign of a Wizard but the rest of the instructions were excellent.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.