Unable to analyse table with binary and multiple choice data with a pivot table

[mark1987__]

Hi all!

I am having troubles converting some data stored in an Excel table into a meaningful pivot table.

The table contains data from post-sales surveys with clients + other internal and external data.

I have built the table ('original table' tab in the attached Excel) so that every row represents a sales opportunity. There are columns for the client name, the outcome (win or loss), the date, the region, the product sold, and the competitor that won or lost.

Troubles start with columns H3 to M3. Each client can select up to 3 reasons for which they chose our product or, alternatively, a competitor's product. I have created a column for each possible reason. While this makes it easy to filter the data in the table; I am unable to aggregate this data with a pivot table (e.g. I cannot aggregate all win reasons by region or client into a single pivot table column) and I am forced to do manual calculations every time I need to do some analysis.

So I thought I was maybe using the wrong table and I have created a new one that has a row for each win/loss reason a client selected ('new table' tab in the Excel).
While I can finally summaries win/loss reasons through a pivot table now; I have lost the ability to count unique opportunities (e.g I cannot say how many times a specific client hasn't chosen us, as client names are repeated several times for the same opportunity).

Is anyone able to suggest a way to overcome this?
Thanks a lot!

[JeteMc]

Hello mark1987 and Welcome to Excel Forum.
This proposal adds a column to the "new table".
The added column ("Count") is populated using: =COUNTIFS(A$4:A4,A4)
The added column is then used as the filter for the pivot table and only 1's are selected.
Client Name is placed in the Rows area and Outcome is placed in both the Columns and Values area.
If this isn't what you are looking for in finding the number of times a specific client has or has not chosen your company then please manually show us what you would like the result to display so that we may attempt to replicate in pivot table.
Please update your profile to include the version of Excel that you are using (i.e. 2010, 2016, 365 etc.) as xp is not a version of Excel and knowing which version you are using may make it easier to help.
Let us know if you have any questions.

[mark1987__]

Thanks so much, JeteMc! It worked perfectly!


Thanks for the heads-up as well; I have edited my profile now.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.