Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Counting number of instances of different scores in a filtered list

Posted on 2014-11-23
Medium Priority
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 50

Expert Comment

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

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 600 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?
Technology Partners: 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 1400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

580 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