Count Items in List with Excel Pivot Table

If you have a long list of items, you could use formulas to count how many times each item occurs in the list. It would take a few steps, including pulling a list of unique items from the list, then creating a formula to count each item.

I find it quicker and easier to create a pivot table to count the list items. You’ll have to add a heading to the list, if there isn’t one already. Then, create a pivot table from the list, with the same field in the Row Labels area and the Values area.

Watch the Video

To see the steps for using a pivot table to count items in a list, watch this short video.

___________

You may also like...

7 Responses

  1. Ed says:

    Hi Debra,

    I’m a big fan of PivotTables, but I also like this kb article on counting unique values
    http://office.microsoft.com/en-us/excel/HP030561181033.aspx

    The formulas explained in the article are a very clever way of counting uniques.

    As long as there are no blank cells in the list, you can use the simplified formula shown below, and it does not have to be entered as an array formula.

    =SUM(IF(FREQUENCY(MATCH(MyList,MyList,0),MATCH(MyList,MyList,0))>0,1))

  2. Thanks Ed! It’s good to know the alternatives.

  3. Grouping and counting with Pivot Tables « Excel Tips Monster says:

    […] | Leave a Comment Tags: count, list, pivot-table There’s a video up on Contextures that shows how to count items of the same type in a list using a pivot […]

  4. […] at Contextures Blog shows us that sometimes built-in features are the best ones to use. In Count Items in List with Excel Pivot Table uses a Pivot Table to group items and count them. Otherwise you’d need a bunch of formulas, and […]

  5. pd tiwari says:

    Can it be possible to count the item less but show the item in the list. Is there any formula?

    I want that the pivot table show all the item but count less. As a item is list for two purposes but we wannt consider it one item.

  6. KAREN GEE says:

    how can I count in excel “how many times the machine was cleaned?

  1. May 29, 2013

    […] at Contextures Blog shows us that sometimes built-in features are the best ones to use. In Count Items in List with Excel Pivot Table uses a Pivot Table to group items and count them. Otherwise you'd need a bunch of formulas, and […]

Leave a Reply to Ed Cancel reply

Your email address will not be published. Required fields are marked *