Need help on multi-level pivot summry

[afzanmeo]

Hi all,

I need help on a survey table that is of following format. Sr# contains 318 rows and there are 30 columns named 1 through 30. I want to create a summary using 'pivot table' that helps me in identifying how many questions have an answer 'a', 'b', 'c', 'd', and '0'. Would appreciate a quick turnaround as I need to submit the results for my dissertation.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Sr#[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]d[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]a[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]b[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]a[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[nils7]

Hi afzanmeo,

what exactly do you want to summarise?

a) how many a,b,c,d or 0 you have in each of the 30 columns?

or

b) how many a,b,c,d or 0 you have in each of the 318 rows?

or

c) how many a,b,c,d or 0 you have in the whole table?


Cheers,

Nico

 

[afzanmeo]

Hi Nico,

I need answers to question (a) primarily. Would appreciate an answer to (b) but on second priority.

Thanks,

Hi afzanmeo,

what exactly do you want to summarise?

a) how many a,b,c,d or 0 you have in each of the 30 columns?

or

b) how many a,b,c,d or 0 you have in each of the 318 rows?

or

c) how many a,b,c,d or 0 you have in the whole table?


Cheers,

Nico

 

[nils7]

Hi afzanmeo,

I wouldn't bother with pivot tables but use countif instead

assuming that your first question data is in row B from B2:B319,

for (a)

just use a countif formula below the data. Put the following four formula into cell B320 to B324

=COUNTIF(B$2:B$319,"a")
=COUNTIF(B$2:B$319,"b")
=COUNTIF(B$2:B$319,"c")
=COUNTIF(B$2:B$319,"d")
=COUNTIF(B$2:B$319,"0")

You can then drag these formulas across all required columns.


for (b)

Use the same formula, but put the following into AF2, AG2, AH2, AI2 and AJ2
=COUNTIF($B2:$AG2,"a")
=COUNTIF($B2:$AG2,"b")
=COUNTIF($B2:$AG2,"c")
=COUNTIF($B2:$AG2,"d")
=COUNTIF($B2:$AG2,"0")

then drag down as far as you need.

Hope this helps,

Nico