+ Reply to Thread
Results 1 to 2 of 2

Unduplicated countif across multiple sheets

  1. #1
    Registered User
    Join Date
    07-02-2018
    Location
    Phoenix
    MS-Off Ver
    2016
    Posts
    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. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    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.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. CountIF with multiple sheets
    By ExcelDummy77 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2015, 04:12 PM
  2. countif for multiple sheets
    By roofi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 06:51 AM
  3. Countif, Multiple Criteria, Multiple Sheets, Excel 2003
    By shyammankoo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2013, 06:50 AM
  4. Replies: 15
    Last Post: 03-11-2012, 10:22 PM
  5. COUNTIF multiple sheets
    By Schalk in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2011, 06:33 AM
  6. CountIf across multiple sheets
    By Zhenya in forum Excel General
    Replies: 2
    Last Post: 09-16-2011, 12:56 PM
  7. COUNTIF across multiple sheets
    By Gizmo63 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2006, 06:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1