Counting only the unique names in 3 columns on separate worksheets.

[RYLEE.HART]

I am making a client register for a homeless shelter. I get a total of individual clients at the end of each month. I do not want to count repeat clients twice. The clients names are in column C6:C36. For this I use the formula:

=SUMPRODUCT((C6:C36<>"")/COUNTIF(C6:C36,C6:C36&""))


I now need to develop a formula for the Quarterly report (separate worksheet) that gives only the individuals for the 3 month period (each month is on a separate worksheet) So if someone stayed in January and again in March, they should only be counted once. Is there a formula that can give the total number of individuals from 3 separate columns on different worksheets without counting repeat clients?
Edit/Delete Message

[dflak]

The attached is a bit kludgy, but it works.

The name data is in Tabs Jan, Feb and Mar in cells C6:C36. I made a helper Sheet called Q1 Data. On this sheet Cells A2:A32 are linked to Cells C6:C36 on the Jan Tab. The next 21 cells in column A are linked to cells C6:C36 on the Feb tab and similarly for Mar.

There is a helper column called Not Blank. If the name is blank on one of these sheets, the link returns 0. We don't want to count these. I used this 94-row range as the source for a pivot table that gives the list of unique names. I filtered out the zeros.

To get the unique count use: =COUNTA('Q1 Data'!D:D)-2 The reason for the minus 2 is that we just want to count the names, not the headers ("Not Blank" and "Row Labels").

[candybg]

You might want to consider a different setup for your data so that you could get that data much more easily.
I'd suggest that all your data be on a single worksheet, with a column added for the MONTH of service.
Then a Pivot Table could gather your data and give you any info you might want in seconds each month/quarter/year.

[RYLEE.HART]

Thank you!