title: Using a PivotTable to get the count of a maximum value
body: <p>I'm using Excel 2007 (which, sadly, lacks many of the cool pivot table features included with 2010 and beyond) and have some data for which I'm attempting to get a count of the number of times each maximum value occurs for a given <code>SiteID</code>.</p>
  
  <p>Each site has the same patients that come in, and I need to know how many patients have been in for <code>visit 1</code>, <code>visit 2</code>, etc. This information is used to determine how much material to send to a give site for a clinical trial. I have something like:  </p>
  
  <pre>SiteID  |PatientID  |VisitNumber  
  1001    |101        |1  
  1001    |101        |2  
  1001    |102        |1  
  1001    |102        |2  
  1001    |102        |3  
  3004    |110        |1  
  3004    |110        |2  
  3004    |110        |3  
  3004    |123        |1  
  3004    |123        |2  
  3004    |123        |3</pre>  
  
  <p>The result for this sample data would be:  </p>
  
  <p>site 1001, 1 patient at <code>visit 2</code> and 1 at <code>visit 3</code><br>
  site 3004, 2 patients at <code>visit 3</code></p>
  
  <p>I can do this fairly easily with a formula and some other tools, but my coworker wants it to be in the form of a PivotTable. If I were doing this myself, I'd use this array formula:  </p>
  
  <pre><code>{=MAX(IF(IF($A$2:$A$2267=A2,$D$2:$D$2267,FALSE)=D2,$F$2:$F$2267,FALSE))}  
  </code></pre>
  
  <p>on each line, and then I'd use remove duplicates and a COUNTIFS() formula to get the result.  </p>
  
  <p>Surely a PivotTable has an easier solution?</p>