Question in building a pivot report | General Excel Questions & Answers

Question in building a pivot report

lea cohen

Member

Members

Forum Posts: 30

Member Since:
March 10, 2016

Offline

July 6, 2017 - 11:54 pm

hello

I have here a table of data that I built in two forms

And I need a report to show me how many students there was in January, February, and more

In Table 1 it is more convenient for me to type each student in one line

But I can not make a Pivot report from him that shows me the student count every month.

In Table 2 I wrote each student 3 times in 3 months

But in the Pivot Account count one student is counted three times

And instead of showing me that in the English course there are 2 students he shows me the total amount

To create a report that will sum up a number of students each month and each course separately

How do I build a data table and what report do I create?

thany you!!

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

July 7, 2017 - 7:00 am

Hi Lea,

Not sure why you want to go for the pivot table?

Here a possible solution with summing the countifs. It doesn't look nice, but works good I think.

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

July 7, 2017 - 12:08 pm

Hi Lea

I noticed that there are difference in the Month between Table1 and Table2. Example: Chana have months June-August in Table1 but April-June in Table2. That is why your Pivottable is incorrect.

If you enter the Month similarly in both Table1 and Table2, the PivotTable will give you the answer just like what Frans Visser got using Formulas.

Hope this helps.

Sunny

lea cohen

Member

Members

Forum Posts: 30

Member Since:
March 10, 2016

Offline

July 7, 2017 - 4:32 pm

Thank you very much for the detailed answer! Smile
I added his payment every month and I already have a summary for each student
And I would like to receive in a separate table the names of the months and the total amount that entered that month
Thank you