Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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 46

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?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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