Counting two columns of data

Seeker63 asked on



I have a spreadsheet with account names in column A and the year the account ran in Column B. There are over 260,000 entries in column A and only six unique entries in column B (years 2008 through 2013).


The 260,000 account names in column A are not unique. there are many duplicates. For example let's say the XYZ Corp had 500 orders in 2013, 650 orders in 2010 and 50 orders in 2008.


I would like to create a pivot table pivot that shows how many years each account placed orders, not the total numbers of orders. For example, I would like to show the number "3" for the XYZ Corp. because they placed orders in three years. Whenever I use the "Count" value setting in my pivot table, it shows the total number of orders placed not the total years.   


Can I accomplish this in the pivot table or do I have to add another column of data that counts the number of years before I pivot?


Any help would be greatly appreciated.





1 person had this question

Abuse history

The answered status icon Answer
V_Agarwal replied on
You may give this formula at C2 


and then drag it down. Now do the pivot as per screen shot.
It will give you desired result. 
You may exclude/skip the year field (column area) in pivot, if desired. 
Be the first person to mark this helpful

Abuse history