Creating Crosstabs in Excel

0

I have a survey asking about mobile device usage, but people have the option of checking more than one device...so folks may check iPad and Android phone, or folks can only check iPhone, etc.

I am trying to use Pivot Tables in Excel to have cross-tabs, but the rows are nested rather than stacked.

Nested: enter image description here

Stacked (from SurveyMonkey): enter image description here

I want them stacked rather than nested. How can I accomplish this in Excel?

Thanks!

SnowboardBruin

Posted 2012-12-06T00:35:33.223

Reputation: 131

Answers

1

I’m not at all sure that I understand your question, but does this

enter image description here

look something like what you want (or at least a step in the right direction)?  Here are the formulae:

enter image description here

Here are columns F-H in a form that you can copy&paste:

       =$A$1                                =$B$1
=$A$1  =COUNTIFS(A2:A17,">0")               =COUNTIFS(A2:A17,">0",B2:B17,">0")
=$B$1  =COUNTIFS(A2:A17,">0",B2:B17,">0")   =COUNTIFS(B2:B17,">0")
=$C$1  =COUNTIFS(A2:A17,">0",C2:C17,">0")   =COUNTIFS(B2:B17,">0",C2:C17,">0")

Scott

Posted 2012-12-06T00:35:33.223

Reputation: 17 653