Structuring Pivot Table

Topic

New Reply

Dear All,

I have this table of data as shown below.

35662-Data

I converted it to pivot table and would like to find out the number of ‘yes’ and ‘no’ in each category.

35663-543966_10151885685153579_1759810623_n

However I tried adding on e.g Count of Women, Count of Man but it does not carry on in Column C and D respectively.

How do I do it such that the next category will be on Column C then D then E etc?

Thank you for your help!! Appreciate much!

Reply | Quote

Replies

Could you attach an example workbook with what you want the table to look like and what results you expect. I don’t think a Pivot table is what you are after, your dataset is not set up to use a pivot table like I think you want.

I think you only need a COUNTIF formula.

If you put in B1:G1 of the summary sheet your headings: Parent’s Life to Fashion
In A2:B2 Enter Yes and No, respectively

Then in B2: (change the Sheet name to match the sheet name the data is on, expand the range as desired)
=COUNTIF(Sheet1!B$3:B$22,$A2)

Then you can copy B2 and paste in B2:G3 and you will have the Yes and No counts for each of the categories.

Instead of a summary table, I typically would insert the rows above the table to show the summaries on the same page and then freeze the frames.

Steve

Reply | Quote

35668-sampleUnfortunately I’ve been told to use pivot table only.

Is there a way to change the table structure such that it can product the pivot table i want?

I’ve attached a sample of my excel and the end results that I want.

Thank you for your help!

Reply | Quote

Why use a pivot table if the COUNTIF method I gave you gives that exact setup without rearranging the data. (see yellow area with orange calcs)

If you want a pivot table analysis, th data must be arranged in a combined table, where the categories are in their own column not 5 separate columns. (see Blue area) and the resulting pivot table is in green.

Steve

Reply | Quote

Thanks guy so much for your help!!

Reply | Quote