Multiple value columns in Pivot table- mistake: repeating column1 value counts in columnn2

Dear all,

I have tried now for several days to solve this problem and would be so thankful for any help.

I use Excel Mac 2011 and have the problem that if I want to display the count for values in multiple columns side by side, the count for column 2 repeats the same numbers as for count in columns 1.

An example I have: in the raw data, there are households who can hold up to 2 types of transportation items. There are 2 columns for each indicating type of transport households have. Q1_1 and Q1_2.
Each of these options are coded i.e. bike, car, bus are numbers from 1-8 as there are 8 options.

What I want: I want to create a pivot table that shows the counts for all households for Q1_1 and Q1_2 i.e. the two types of transport they may possess out of 8 different options.

What I did: There is no problem in creating the counts for either Q1_1 or Q1_2. Row label then is either of the one and the matching count of in the value field. It shows me that 5 households have a bike, 18 a car etc.
But if I want to display both counts next to each other, it presents the same counts for Q1_2 as for Q1_1, i.e. they show the 5 and 18 etc again. It seems the problem is the label row. I tried all solutions indicated on the web, fora etc e.g. to move both Q1_1 and Q1_2 into row label, then move Q1_2 into columns labels. That however just sorted the transportation mode from Q1_2 underneath Q_1 which is not what I want. I also tried to use 'sum of' 'count numbers' to see if it changes the way it counts the second transportation item. No success.

A note: the range from the transportation items 1-8 is not covered by the second item. However, in other examples i.e. yes, no questions I have the same issue even though answer options are completely congruent.

Is there anyone please who can help me? I have tried all I can and it seems I need to give up.

Thank you!!

Best, Nina

Dear all,

I hope this helps to illustrate the problem.

Thanks so much for any help!

Best regards,
Nina