Results 1 to 4 of 4

Count unique value from a date range with multiple criteria

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-27-2017
    Location
    Dhaka
    MS-Off Ver
    MS Excel 2019/ 64 bit
    Posts
    159

    Count unique value from a date range with multiple criteria

    Dear Expert,
    I want to find out how many unique value form a date range on my worksheet & also consider another 2 criteria.
    Let me explain with my attached file:
    On attached file "Audit sheet" d5 will be my result cell & the same sheet c3 is my date range where A5, C5 another 2 criteria.
    My data table is on same file another sheet "Master Data".

    C3 is a first date of a month but when i want to get the result then this cell data will consider as a date range like if c3 is 4/1/2020 then at formula it will be consider 1st day to last day of this month like 4/1/2020 is 4/1/2020-4/30/2020, 5/1/2020 is 5/1/2020-5/31/2020.

    C3 (Date) range will match "Master Data" column t2:T20000.
    A5 (Factory) data will match "Master Data" column w2:w20000.
    C5 (Buyer) data will match "Master Data" column a2:a20000.

    Although i am trying a formula which is below but when i use that formula, my sheet is very slower & not workable.
    So i need i simple lite formula or any other way to get my result. Below is my formula & help me in this regard.

    Formula: copy to clipboard
    =SUM(IF((A5='Master Data'!$W$2:$W$20000)*('Master Data'!T$2:$T$20000<=EOMONTH(C3,0))*('Master Data'!$T$2:$T$20000>=C3)*(C5='Master Data'!$A$2:$A$20000),1/COUNTIFS('Master Data'!$W$2:$W$20000,A5,'Master Data'!$I$2:$I$20000,'Master Data'!$I$2:$I$20000,'Master Data'!$T$2:$T$20000,"<="&EOMONTH(C3,0),'Master Data'!$T$2:$T$20000,">="&C3,'Master Data'!$A$2:$A$20000,C5)),0)


    Attached file for your ref.

    Best Regards
    Wahid
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 04-28-2017, 11:06 AM
  2. Find count of unique values with multiple criteria inlcuding date ranges
    By jdooley in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-21-2016, 01:05 PM
  3. [SOLVED] Count and sum with multiple criteria including date range
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2016, 04:46 PM
  4. [SOLVED] Count if multiple criteria and within date range
    By T15K in forum Excel General
    Replies: 5
    Last Post: 01-10-2016, 06:22 AM
  5. Adding a date range to a unique data count based on multiple criteria
    By Matthew_Smith86 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-10-2015, 08:50 AM
  6. Count - multiple criteria, multiple sheets, and date range.
    By threecliffs in forum Excel General
    Replies: 6
    Last Post: 06-14-2011, 01:36 PM
  7. Replies: 4
    Last Post: 04-08-2011, 05:24 PM

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