Excel Pivot How to count words in a column

Question

I would like to achieve what I've dummied up in the third image. However, I cannot get the Pivot function to count the word occurances by column. I believe I am close . . . (see first and second images showing pivot and source). Thank you in advance!

Answer 1

Hi @nancy ross,  

Welcome to Microsoft Q&A forum. 

You were trying to use a PivotTable to count the total number of times each person's name appeared in each specific role column (Class President, Secretary, etc.), but the PivotTable wasn't grouping and counting the names by their respective columns as you intended. 

To achieve the desired outcome, start by restructuring (unpivoting) your data. The simplest way to accomplish this is by using Excel’s built-in Power Query tool.

While my dataset is slightly different from your example, it is intended for the same objective. I have provided my data below for your review and testing.

Please follow the steps outlined below:

Step 1: Unpivot your data with Power Query 

1. Select your entire data table (including the headers). 
2. Go to the Data tab on the ribbon. 
3. In the "Get and Transform Data" section, click From Table/Range.
4. In the editor, select all of the role columns (click the "Class President" header, then hold Shift and click the last column header, "Names"). 
5. Go to the Transform tab. 
6. Click the Unpivot Columns button.
   
7. Now, go to the Home tab and click Close & Load. This will create a new worksheet in your workbook with this new, properly formatted table. 

Step 2: Create your PivotTable 

Now that you have your new unpivoted data, creating the PivotTable is simple. 

1. Click anywhere inside your new two-column table (the one you just loaded from Power Query). 
2. Go to the Insert tab and click PivotTable. 
3. In the "PivotTable Fields" pane on the right: 

• Drag Name to the Rows area. 
• Drag Role to the Columns area. 
  • Drag Name again to the Values area.
    

It should automatically set to Count of Name, and your PivotTable will instantly look exactly like the example in your third image.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.  

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Assuming your data is in cells A1:D11, please try the following formula. This is a single formula and keeps the same header structure (so it doesn't change its order).

=LET(d, A1:D11, dr, DROP(d, 1), s, SORT(UNIQUE(TOCOL(dr, 1))), VSTACK(HSTACK("Name", TAKE(d, 1)), HSTACK(s, IFERROR(1 / (1 / TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYCOL(dr, LAMBDA(a, ARRAYTOTEXT(COUNTIF(a, s))))), ", ", ";"))), ""))))

Hope this helps.

Answer 3

Hi,

In cell H1, enter this formula

=LET(d,A2:D6,t,TOCOL(d),PIVOTBY(t,TOCOL(IF(A2:A6>0,A1:D1)),t,COUNTA,,0,,0))

Hope this helps.