SOLVED

Data Sheet - Pivot Table -One Result Return

%3CLINGO-SUB%20id%3D%22lingo-sub-2796618%22%20slang%3D%22en-US%22%3EData%20Sheet%20-%20Pivot%20Table%20-One%20Result%20Return%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796618%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20data%20file%20where%20I%20would%20like%20to%20be%20able%20to%20return%20one%20result%20when%20there%20are%20multiple%20duplicate%20entries%20for%20that%20same%20results%20so%20when%20combined%2C%20it%20does%20not%20add%20up%20the%20total%20but%20instead%20show%20that%20ONE%20result.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20may%20have%20one%20estimate%20budget%20that%20contains%20multiple%20entries%20with%20the%20same%20repeated%20number.%20When%20view%20within%20a%20pivot%20table%2C%20it%20adds%20up%20the%20total%20to%20give%20me%20an%20inflated%20number%20instead%20to%20give%20me%20simply%20the%20estimate%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2796618%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2796723%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Sheet%20-%20Pivot%20Table%20-One%20Result%20Return%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1170457%22%20target%3D%22_blank%22%3E%40Jessjess1150%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20using%20Office%20365%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM(UNIQUE(H2%3AH13))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20older%20Excel%20version%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUMPRODUCT(1%2FCOUNTIF(A2%3AA13%2CA2%3AA13)%2CH2%3AH13)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2796745%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Sheet%20-%20Pivot%20Table%20-One%20Result%20Return%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796745%22%20slang%3D%22en-US%22%3EHello%20-%20thanks%20so%20much%20for%20your%20help!%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20still%20returns%20duplicates%20entries.%3C%2FLINGO-BODY%3E
New Contributor

Good afternoon!

 

I have a data file where I would like to be able to return one result when there are multiple duplicate entries for that same results so when combined, it does not add up the total but instead show that ONE result. 

 

For example, I may have one estimate budget that contains multiple entries with the same repeated number. When view within a pivot table, it adds up the total to give me an inflated number instead to give me simply the estimate number.

 

 

5 Replies

@Jessjess1150 

If you are using Office 365

 

=SUM(UNIQUE(H2:H13))

 

For older Excel version 

=SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13),H2:H13)
Hello - thanks so much for your help!

It still returns duplicates entries.

@Jessjess1150 

Find attached

best response confirmed by Jessjess1150 (New Contributor)
Solution
If it solves your problem, please flag it as solved.
Remember that hitting the like button is less costly than buying me a coffee.
Thank you very much - this sincerely appreciated!