• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
86 views

Counting two columns of data

Seeker63 asked on

Hello,

 

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.

 

Thanks.

 

Chris

1 person had this question

Abuse history


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

=--(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1)

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. 
Vijay
Be the first person to mark this helpful

Abuse history


progress