count unique text values based on condition in ano

问题:

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

回答1:

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.}

回答2:

You formula works with a few tweaks:

1. The date strings need to be converted to date values using DateValue() function

2. The comparison operators for the dates are interchanged (it should be <= 2016 date and >= 2013 date)

3. The comparison against "digital" needs to be reversed

4. The innermost IF statement needs a zero as value for situations where the condition fails

5. I removed the outermost parentheses which are not needed

Here is the modified version that works:

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