My Pivot Table challenge

[medelste]

I'm sure this is an easy one, but I can't wrap my mind around it.

I have a data table like this (sorry for the image, the XL2BB macro isn't activating for me)

Here's what I'd like the pivot table to summarize for me: the number of Pass's and Fail's each student received on each date. Just like this that I typed by hand:

But I'm unable to make that happen using the Pivot Table tool. This is as close as I come. As you can see, Mike's number get repeated for each student.

I understand why each column is duplicated: I added Mike's results as a Row element in the pivot table to get the Words "Pass" and "Fail" in that column.

I can make it work using a Pivot Table for each student, but I don't want to do that, I'd like them all right next to each other. How do I do that?

If it matters, I'm using Microsoft 365.

Thank you!!

 

[Automatrix]

Power Query solution: Start by turning your data into a table, and connect to it with Power Query. Select the columns with students names, and use the unPivot Columns action. This will give you a data table like this (you can rename the columns), which is more suited to pivot tables:

Book5
	A	B	C	D
4	Date	Quiz Topic	Attribute	Value
5	10/15/22	History	Mike	Fail
6	10/15/22	History	Samantha	Pass
7
8	Count of Attribute	Column Labels
9	Row Labels	Mike	Samantha	Grand Total
10	10/15/22	1	1	2
11	Fail	1		1
12	Pass		1	1
13	Grand Total	1	1	2
Sheet1

 

[medelste]

Thank you!