Counting number of instances of different scores in a filtered list

Posted on 2014-11-23
Last Modified: 2014-11-23
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
Question by:colin_thames
LVL 47

Expert Comment

by:Martin Liss
ID: 40460914
Can you attach a workbook please?
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 150 total points
ID: 40460939

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

Author Comment

ID: 40460960
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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 24

Expert Comment

by:Phillip Burton
ID: 40460974
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")
LVL 24

Expert Comment

by:Phillip Burton
ID: 40460977
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,

Author Comment

ID: 40460989
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?
LVL 81

Accepted Solution

byundt earned 350 total points
ID: 40461022
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.

Author Closing Comment

ID: 40461121
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question