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:
=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
Bookmarks