Hello,
In the attached spreadsheet, I have two tabs.
Tab 1: "SourceData" where thousands of names are entered and dates are entered when a corresponding benefit is submitted for each person (an individual person is listed only once; the benefits can be submitted in the same month or across multiple months)
Tab 2: "Summary" where I am looking to calculate the unduplicated number of people for whom benefits were submitted in a particular month. If one person had three benfits submitted for them in one month, I want that to count as one person for that month (for exapmle, in row 4 of "SourceData," that would count as one person in the month of January 2012). If someone had a benefit submitted during November and a benefit submitted in February, I want that to count as two people (one for the month of November 2011 and one for the month of February 2012 - example, row 5 in "SourceData")
In the "Summary" tab I show what the totals should look like based on a manual count (this is shown in row 8).
I have tried doing sumproduct and sumiffrequency but it seems to basically count all of the dates and give me those totals. Any idea how to do this with a formula? This is a subset of a much larger spreadsheet, so if possible, I would prefer not to have to create additional columns in the "SourceData" to pull out all possible months and years and then tabulate that.
Any help would be greatly appreciate. Many thanks in advance.
Best,
Caroline
Bookmarks