Pivot table counts visits by month when one visit has multiple lines

To get a count of unique visits add a helper column (Visits) to the source data with the formula:

Please Login or Register  to view this content.

Your Pivot Table would then have row labels of Facilities and Visits, with the subtotals and grand totals turned off and change count of Visits to Sum of Visits. It assumes that on 2/4/16 the EBL got two visits, one annual and the other C/O; S/R If that is not the case and only the date matters, change the formula to:

Please Login or Register  to view this content.

To get the visits by month the row labels are date and facility with the dates grouped by month and the subtotals and grand totals turned off.
Here is a copy of your file with the helper column and pivot tables applied: Copy of Pivot Table Sample.xls
Let me know if you have any questions.

I'm going to take a slightly different approach. I am not assuming that the Facilities are in order, and that a visit consists of the combination of the facility name and the date of the visit. So I am using two helper columns:

The first is called composite. Whenever you need to evaluate a row of data based on several criteria, one way to do it is to use what I call a "Composite Key" - this isn't an official Excel term, but it is analogous to composite keys in databases. The composite key is the concatenation of those columns that make the record unique, in this case, the facility name and date. =A2&":"&B2.

I usually put a delimiter between the pieces of the composite key to make it more readable. It doesn't matter that the date is displayed as its numerical equivalent.

The second helper column I call Unique and it has the formula: =MATCH(F2,F:F,0)=ROW(). This formula looks at the composite key on the row and compares it to all the other composite keys on the row. The MATCH portion of the formula returns the row number of the first instance of the composite key. If that happens to match the row number on which the formula is found (the ROW part), then this expression is True. Match finds that the first instance of the key is on a previous row, then this expression is false. So TRUE means the first instance of the key and FALSE is all the duplicates of the key.

This method works regardless of whether the list is sorted.

I then set up the pivot table so the report filter is the newly-created Unique column and the rows are Date (Grouped by Month) and the values is the count of the facilities.

One question. If you have Excel 2010 then why are you still using *.xls format?

I'm going to take a slightly different approach. I am not assuming that the Facilities are in order, and that a visit consists of the combination of the facility name and the date of the visit. So I am using two helper columns:

The first is called composite. Whenever you need to evaluate a row of data based on several criteria, one way to do it is to use what I call a "Composite Key" - this isn't an official Excel term, but it is analogous to composite keys in databases. The composite key is the concatenation of those columns that make the record unique, in this case, the facility name and date. =A2&":"&B2.

I usually put a delimiter between the pieces of the composite key to make it more readable. It doesn't matter that the date is displayed as its numerical equivalent.

The second helper column I call Unique and it has the formula: =MATCH(F2,F:F,0)=ROW(). This formula looks at the composite key on the row and compares it to all the other composite keys in the column. The MATCH portion of the formula returns the row number of the first instance of the composite key. If that happens to match the row number on which the formula is found (the ROW part), then this expression is True. Match finds that the first instance of the key is on a previous row, then this expression is false. So TRUE means the first instance of the key and FALSE is all the duplicates of the key.

This method works regardless of whether the list is sorted.

I then set up the pivot table so the report filter is the newly-created Unique column and the rows are Date (Grouped by Month) and the values is the count of the facilities.

One question. If you have Excel 2010 then why are you still using *.xls format?