Excel 2010 --> Pivot Table "Distinct" value needed

We have a query that we need the "distinct" value count.  

On the attached screenshot, you will see per week we need a count of locations that ordered.  not the "# of orders".

For example,  Fort Bragg Honeycutt Shoppette has 2 orders for the week of 12/2/2013.  We want to only have it count "1" because they ordered.

I have found that there is an add-on, but we are not permitted to install on the Company's Terminal Server.

Thank you for your assistance!
pivot-screenshot.jpg
sharpapproachAsked:
Who is Participating?
 
JorgenConnect With a Mentor Commented:
Hi SharpApproach

I believe, that I solved a similar problem for a colleague last week (at least if I understand your question correctly).

I have attached the file, and in the file, you can see where I found my inspiration. I used two "Help Columns in my dataset - which did the trick.

I can not see which version of Excel you have, but in 2013 you should be able to use "Distinct Count" in your pivot, which is doing the same as I do in the attached file.

regards

Jørgen
Count-Text-in-Pivot.xlsx
0
 
RobOwner (Aidellio)Commented:
Are you able to attach the workbook, or sample, as it may be possible to add an array formula against your source data, like the attached sample

{=MAX(IF(C2=$C$2:$C$100,IF(F2=$F$2:$F$100,$D$2:$D$100,0),0))}
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.

All Courses

From novice to tech pro — start learning today.