Question
85 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