1

I have a fairly large dataset that looks similar to this if I limited it at four rows:

Staffer      Name         Registration       Due         Confirmation
Julie        Ryan         Registered         Unpaid      Not Confirmed
Julie        Sarah        Unregistered       Unpaid      Not Confirmed
Sam          Ben          Registered         Paid        Confirmed
Sam          Jack         Registered         Paid        Not Confirmed

I want to use a PivotTable to do the calculations of registered % of total, paid % of total, and confirmed % of total by staff member. I can do this fairly easily one at a time by using 'show data as' % of row but can't figure out how to get all three columns with a total at the end without everything layering on top of each other and making it really messy. The closest I can get is making four separate PivotTables and hiding all the unwanted columns - which is really annoying for a report that has to be generated daily.

Ideally, something like this would be the output for the simple example I gave above:

        Registered  Paid    Confirmed   Total
Julie   50%         0%      0%          2
Sam     100%        100%    50%         2
Total   75%         50%     25%         4

Keeping this in a PivotTable format would be great because people love being able to click on their totals to see the names of the people coming, but if better to do it outside of one, I'm open to that too.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Ryan
  • 501
  • 1
  • 12
  • 26

1 Answers1

1

Formulas can do that.

Arrange the unique names similar to the screenshot and apply the formulas:

B8  =COUNTIFS($A$1:$A$5,$A8,C$1:C$5,B$7)/COUNTIF($A$1:$A$5,$A8) ' Copy down all name rows and across to column D
E8  =COUNTIF($A$1:$A$5,$A8)  ' copy down all names

Totals  
B10 =COUNTIF(C$2:C$5,"registered")/COUNTA($A$2:$A$5) ' copy across to column D
E10 =SUM(E8:E9)

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • I like this approach +1. You could get the unique names by using a pivot or an array formula like =INDEX(a$2:a$5,MATCH(0,COUNTIF(a$7:a7,a$2:a$5)) if you wanted to go all-formula. – Tom Sharpe Aug 12 '15 at 08:25
  • @teylyn Thanks so much! This works well -- as I said, I would prefer to do it with PivotTables just so the people I'm sending it to could click on their part of the table and open a new tab to see their people, but if this is the only good way to do it, I'm open to that also. – Ryan Aug 13 '15 at 04:39
  • As far as I know, regular pivot tables don't have the functions to do the calculations in this way. Power Pivot with Dax formulas may be an option, but then the people who open the file would need Power Pivot as well if they want to apply filters. – teylyn Aug 16 '15 at 02:46
  • If my suggestion solves your problem, please mark it as the answer. – teylyn Aug 16 '15 at 02:47