Using Count in Pivot Table when Summarizing

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,
I've created this pivot table to summarize the number of distinct dates a teacher was online. Teachers are in the rows. Dates are columns.
1598454221849.png

In the Grand Total column I want to show the number of dates the teachers were online. For example, in the first row in the grand total equals 2 for total lessons graded on 8/24. I would like, instead, the cell to calculate the number of days, which in the case of row one, would be 1. Out of the two possible days available to work, 8/11 and 8/24, Robert worked only on the 24th. What I don't understand how to do is change the summary box so that it will count the distinct days. In the value field settings I've tried both Count and Count Numbers and both give me the same result of 2 in my example of row 1. Any help would be greatly appreciated at this point. Thank you.
1598454773807.png
 

Attachments

  • 1598455205291.png
    1598455205291.png
    50.4 KB · Views: 0

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,079
while creating Pivot Table use add to Data Model then you'll get count distinct possibility

add2datamodel.png

distcount.png
 
Last edited:

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I can't click the box Add this data to the Data Model. It is greyed out. Thanks for helping me.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,079
start from the beginning
usually it works with Excel Table or range
you've 365 / 2016 so Data Model is supported
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
90
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you for helping me. I figured out why the selection was greyed out. After I saved the file in 365 I could select it. Great help. Thanks. A
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top