# Unduplicated countif across multiple sheets

1. ## Unduplicated countif across multiple sheets

Hello,

I am creating a workbook to track attendance. The goal is to get an unduplicated count of "total number of individuals attended in one month" and also "total number of times service type used"

The workbook is set up like this: sheet 1 is "month ending" ; followed by 31 sheets for each day of the month (labeled 1st, 2nd, 3rd...30th & 31st) ; there is a final sheet that has a roster of enrolled persons. The roster is used to create a drop down list under the column "name"

I can get an unduplicated count for each sheet, but I do not know how to get an unduplicated count total for all sheets. Using the sumproduct and countif functions how can get an unduplicated total of individuals served for the entire month?

I made an attempt and got stuck...so I am here today. Thank you in advance.

This is the function I tried [=SUMPRODUCT(COUNTIF("'1st'!A5:A154!";"'2nd'!A5:A154";"'3rd'!A5:A154";"'4th'!A5:A154";"'5th'!A5:A154";"'6th'!A5:A154";"'7th'!A5:A154";"'8th'!A5:A154";
"'9th'!A5:A154";"'10th'!A5:A154";"'11th'!A5:A154";"'12th'!A5:A154";"'13th'!A5:A154";"'14th'!A5:A154";"'15th'!A5:A154";"'16th'!A5:A154";
"'17th'!A5:A154";"'18th'!A5:A154";"'19th'!A5:A154";"'20th'!A5:A154";"'21st'!A5:A154";"'22nd'!A5:A154";"'23rd'!A5:A154";"'24th'!A5:A154";
"'25th'!A5:A154";"'26th'!A5:A154";"'27th'!A5:A154";"'28th'!A5:A154";"'29th'!A5:A154";"'30th'!A5:A154";"'31st'!A5:A154" & A5:A154))]

2. ## Re: Unduplicated countif across multiple sheets

My standard reply is "Don't do that!" Use one sheet, with a column for date. Then you can use pivot tables, filters, normal formulas etc.

If you are determined to continue with multple sheets (as you described your workbook structure), you could use a User-Defined-Function (a macro). Copy the code below into a standard codemodule, set a reference to MS scripting Runtime, and use the function like so:

=UniqueCount(A5:A154)

If you want a list of all the unique values, run the macro "GetUniques" When prompted, select A5:A154 on any sheet, and an anchor cell for output.
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)