Pivot Table - Count multiple columns

[minimini99]

I've searched and found 'solutions' to my problem online but the 'solutions' didn't seem to work out for me. Hopefully someone can point me in the right direction.

I'll start by referencing this problem+solution online (Optional reading): excel pivot table - multiple columns - Stack Overflow

Basically, I have a table like so

[TABLE="width: 385"]
<tbody>[TR]
[TD]I like bread[/TD]
[TD]I like cheese[/TD]
[TD]I like milk[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]boy[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]girl[/TD]
[TD]girl[/TD]
[TD]boy[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]boy[/TD]
[TD]boy[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]boy[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]boy[/TD]
[TD]boy[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]girl[/TD]
[TD]boy[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]boy[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]girl[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]girl[/TD]
[TD]boy[/TD]
[TD]girl[/TD]
[/TR]
[TR]
[TD]girl[/TD]
[TD]girl[/TD]
[TD]girl[/TD]
[/TR]
</tbody>[/TABLE]


After using a pivot table, I want the end result to be like so

[TABLE="width: 493"]
<tbody>[TR]
[TD][/TD]
[TD]I like bread[/TD]
[TD]I like cheese[/TD]
[TD]I like milk[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]girl[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Following the solutions online, I create a pivot table, throw "I like milk" into the Row Labels, then throw all three "I like ..." into Values.
I end up with something like this:

(Pic link https://i.imgur.com/ztKhmK1.png )


It looks right at first glance but notice this:
"Count of I like bread" is 4 boys and 6 girls. The correct count would be 7 boys and 3 girls. Same for "Count of I like cheese".

Anyone know what is actually happening and how I can get it to work correctly? Thanks!


Extra task:
Bonus points for someone who can help me get the sum of each row as well, so I would know the number of boys and number of girls. It would look like
[TABLE="width: 557"]
<tbody>[TR]
[TD][/TD]
[TD]I like bread[/TD]
[TD]I like cheese[/TD]
[TD]I like milk[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]boy[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]girl[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Useful]

Hello,

it is possible with "Power Query". If you ready to use this then select your table and from PQ menu Excel Data/FromTable and select all columns go to Transform Unpivot columns. After passing all of steps your PQ code should like this:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"I like bread", type text}, {"I like cheese", type text}, {"I like milk", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
#"Unpivoted Columns"

and you may use pivot table based on this table.

 

[minimini99]

Hello,

it is possible with "Power Query". If you ready to use this then select your table and from PQ menu Excel Data/FromTable and select all columns go to Transform Unpivot columns. After passing all of steps your PQ code should like this:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"I like bread", type text}, {"I like cheese", type text}, {"I like milk", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
#"Unpivoted Columns"

and you may use pivot table based on this table.

I haven't used Power Query before so I'm not familiar with it. Could I know what that does?

 

[Useful]

Power Query download

 

[minimini99]

Power Query download

Alright, I'll take a look into it.

Though do you know why the method the others suggested doesn't seem to work?

 

[Useful]

because all you need is to make unpivot your table. At the result you'll get the table with two columns. One column will repeat with "I like bread", "I like cheese" and "I like milk" and the other column will repeat with "boy" and "girl". And that is the table that you need to use in pivot table. In pivot table fields use first column in "Columns", and use the second column name in "Rows" and "Values" fields.