有一个基于

现在这个公式很难维护，行数不断增加和删除.要创建动态范围，我们可以INDEX 每个完整列并使用 MATCH 将最后一个日期定位为每一列的终止符.

=SUMPRODUCT(((C$2:INDEX(C:C,MATCH(1E+99,C:C))>=F2)*(C$2:INDEX(C:C,MATCH(1E)+99,C:C ))G2)+($B$2:INDEX(B:B, MATCH(1E+99,C:C))E2)))

是的，这看起来很乱，但它动态地添加和减去 SUMPRODUCT 函数处理的行，因此计算效率非常高.

¹ _{有关其工作原理的说明，请参见 使用 SUMPRODUCT() 细分计算唯一性.}

What i need is formula to count unique values in TITLE based on criteria of column FIAP/Medium & Year.

This needs to begin by looking at the date in worksheet column M/Year, the range is 23/03/13 - 01/06/16. It then needs to check column I/FIAP Medium looking for 'Digital' and finally count the unique images in A/Title.

This is the working sheet column

   Title              FIAP/Medium      Year
  Conors Push        Digital         3/23/2013
  Conors Push        Digital         4/1/2013
  Flippin Hell       Digital         4/1/2013
 Narrowing the Gap   Digital         4/1/2013
 The Perfect Storm   Digital         4/8/2013
 Conors Push         Digital         4/12/2013
 Splash and Dash     Digital         4/12/2013
 Wind Power          Digital         4/12/2013
 Conors Push           -             4/28/2013
 Eyes On The Ball      -             4/28/2013
 Narrowing the Gap     -             4/28/2013
 The Bunkers Demise    -             4/28/2013
 A Few Inches More   Digital         5/1/2013
 Entwined            Digital         5/1/2013
 Conors Push         Digital         5/14/2013
 Fosbury Delight     Digital         5/14/2013
 Narrowing The Gap   Digital         5/14/2013
 Sprint For The Lin  Digital         5/14/2013

I have come up with formula

=SUM(--(FREQUENCY(IF(International!M2:M1000>="6/1/2016",IF(International!M2:M1000<="3/23/2013",IF(International!I2:I1000<>"",IF(International!I2:I1000="Digital",MATCH(International!A2:A1000,International!A2:A1000,0))))),ROW(International!A2:A1000)-ROW(International!A2)+1>0)))

but its showing zero value. It may be because date condition requires two condition with one column.

Please help me with this formula or any other method to count unique based on this conditions.Specially dates

There is a fairly common method of a pseudo-COUNTUNIQUE¹ based on the SUMPRODUCT and COUNTIF functions that looks like this.

=SUMPRODUCT(1/COUNTIF(A2:A19, A2:A19&""))

But that does compensate for the conditions. To add conditions, you need to change to a COUNTIFS function. Additionally, both the numerator and denominator of the division operation have to be adjusted by the conditions and the inverse of the conditions in order that you return a true count without erroring on with a #DIV/0!.

=SUMPRODUCT(((C$2:C$19>=F2)*(C$2:C$19<=G2)*($B$2:$B$19=E2))/
             (COUNTIFS(A$2:A$19, A$2:A$19&"", C$2:C$19, ">="&F2, C$2:C$19, "<="&G2, $B$2:$B$19, E2)+
              (C$2:C$19<F2)+(C$2:C$19>G2)+($B$2:$B$19<>E2)))

  

Now that formula is hard to maintain of there are constant additions and deletions to the number of rows. To create dynamic ranges, we can INDEX each full column and use MATCH to locate the last date as the terminator for each column.

=SUMPRODUCT(((C$2:INDEX(C:C, MATCH(1E+99,C:C ))>=F2)*(C$2:INDEX(C:C, MATCH(1E+99,C:C ))<=G2)*($B$2:INDEX(B:B, MATCH(1E+99,C:C ))=E2))/
    (COUNTIFS(A$2:INDEX(A:A, MATCH(1E+99,C:C )), A$2:INDEX(A:A, MATCH(1E+99,C:C ))&"", C$2:INDEX(C:C, MATCH(1E+99,C:C )), ">="&F2, C$2:INDEX(C:C, MATCH(1E+99,C:C )), "<="&G2, B$2:INDEX(B:B, MATCH(1E+99,C:C )), E2)+
      (C$2:INDEX(C:C, MATCH(1E+99,C:C ))<F2)+(C$2:INDEX(C:C, MATCH(1E+99,C:C ))>G2)+($B$2:INDEX(B:B, MATCH(1E+99,C:C ))<>E2)))

Yes, that looks like a mess but it dynamically adds and subtracts rows processed by the SUMPRODUCT function so that calculation efficiency is at a premium.

---

¹ _{For an explanation on how this works see Count Unique with SUMPRODUCT() Breakdown.}