Rachael has attended:
Excel Intermediate course
Creating summary report using COUNTIF or pref. Pivot table
Hi,
I have a list of contacts interested in different 'Knowledge Pools' which represent subject areas these persons are interested in. The data is in columns - 'Name' and 'Knowledge Pool'. Each name appears just once. Under 'Knowledge Pool', the names of the different knowledge pools they are interested in are listed, separated by commas. To examine this data, I used 'text to columns' to separate these.
I then wish to count the the number of occurrences of each 'Knowledge Pool' to determine level of interest in each one. Each person may be interested in more than one Knowledge Pool (up to 20 in total), but the list may come up in different orders, e.g. 'A,C,B,D' for one person and 'C,A,D,F' for another
I would like to produce a summary report of this data. So far I have done this by selecting all the data and using the COUNTIF function for each Knowledge Pool to count no. of occurrences. But this is time consuming. Is there a quicker way?
Thanks
rachael
RE: Creating summary report using COUNTIF or pref. Pivot table
I have also used pivot tables but I can only make it work if I do one for each column i.e. I have data organised as follows:
Name Knowledge Pool1 Knowledge Pool2 Knowledge Pool3
I have to pull 3 pivot tables using knowledge pools as row labels and count of names to give me the numbers interested in each.
Once this is complete, I have to add all the numbers up to get the total interested in each knowledge pool
Again, I'm sure there must be a quicker way!
RE: Creating summary report using COUNTIF or pref. Pivot table
Hi Rachael
First, sorry for taking so long to get back to you.
To find out the occurrences of each Knowledge Pool using a Pivot Table the data really needs to be rearranged in a different format.
For example
Instead of
Al A,C,D,E,F,G
Jane A,B,C,E,G,H
etc
Arrange like this
Name Knowlwdge Pool
Al A
Al C
Al D
Al E
Al F
Al G
Jane A
Jane B
Jane C
Jane E
Jane G
Jane H
John A
Peter B
Peter C
Peter D
Peter E
Peter F
Peter G
Peter H
Rachael A
Rachael B
Rachael C
Rachael D
Then the Pivot Table for this data would be
Row Labels Count of Name
A 4
B 3
C 4
D 3
E 3
F 2
G 3
H 2
Grand Total 24
I've found a way to count the number in each zone without rearranging the data
Name Knowledge Pool A B C D E F G H
Al A,CD,E,F,G 1 0 1 1 1 1 1 0
Jane A,B,C,E,G,H 1 1 1 0 1 0 1 1
John A 1 0 0 0 0 0 0 0
Peter B,C,D,E,F,G.H 0 1 1 1 1 1 1 1
Rachel A,B,C,D 1 1 1 1 0 0 0 0
Totals 4 3 4 3 3 2 3 2 24
Formula
=IF(ISERROR(SEARCH(D$1,$B2)),0,1)
The search function returns an error if it can't find the letter at the top (A,B,C etc)
If so enter 0, otherwise enter 1
Doug
Best STL
Attached files...
Knowledge Pool.xlsxWill be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
