Improve company productivity with a Business Account.Sign Up

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 414
• Last Modified:

Excel-Pivot Tables

I am running Pivot table in Excel.  The spreadsheet contains branch locations and account numbers.  There are rows where the account numbers are duplicated numerous times on the spreadsheet.

When I run the pivot table to obtain a count of the account numbers, the duplicate account numbers are double counting.  If the acount numbers are listed multiple times, I want it to be counted only once.

Is there a way to do this in excel with a pivot table?
0
ArisaAnsar
Asked:
2 Solutions

Commented:
I don't think you can do it without adding a new column, but I would use an array formula like this: if the account numbers are in column A rows 1-50, use this formula-

=SUM(1/COUNTIF(A1:A50,A1:A50))
This is an array formula and must be validated with the combination of the 3
keys: Ctrl+Shift+Enter instead with the single key Enter.  i.e after you type the formula, press the key combination Ctrl+Shift+Enter.
0

Commented:
You will need to add a column to your database that will be 1 for each new account number and 0 for any duplicates.

assuming your account numbers are in column C and your data has headers in row 1 and data starting in row 2, something like this, in any column of row 2, then copy down.
=--(countif(\$C\$2:\$C2,\$C2)=1)

Thomas
0

Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.