根据日期标准之间另一列中的条件计算唯一文本值 [英] count unique text values based on condition in another column between date criteria

问题描述

我需要的是根据FIAP / Medium&年份

这需要从工作表列M / Year中查看日期开始，范围是 23/03/13 - 01/06/16 即可。然后需要检查列I / FIAP中寻找'数字'，最后计算A / Title中的唯一图像。

这是工作表列

 标题FIAP /中年
 Conors Push Digital 3/23/2013 
 Conors推数字4/1/2013 
 Flippin Hell Digital 4/1/2013 
缩小差距数码4/1/2013 
完美风暴数字4/8/2013 
 Concon推数字4/12/2013 
飞溅和破折号4/12/2013 
风力数字4/12/2013 
 Conors推 -  4/28/2013 
眼睛球 -  4/28/2013 
缩小差距 -  4/28/2013 
 Bunkers Demise  -  4/28/2013 
几英寸更多数字5/1/2013 
纠缠数字5/1/2013 
 Conors推数字5/14/2013 
 Fosbury Delight Digital 5/14/2013 
缩小差距数字5/14/2013 
林数字冲刺5/14/2013 
  

我已经想出了公式

  = SUM（ - （FREQUENCY（IF（国际M2：M1000> = 2016年6月1日，IF（国际M2：M1000< = 2013年3月23日，IF（国际!!! I2：I1000<> 中，IF（！国际I2：I1000 = ！数字，MATCH（国际A2：A1000，国际A2：A1000,0））））），ROW（国际A2：A1000） -ROW（International！A2）+ 1> 0）））
  

但它显示零值。这可能是因为日期条件需要两个条件，一列。

请使用此公式或任何其他方法根据此条件计算唯一的值。日期

解决方案

基于

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

  = SUMPRODUCT（（（C $ 2： （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：C，MATCH（1E + 99，C：C）），C $ 2：INDEX（A：A， ，=& F2，C $ 2：INDEX（C：C，MATCH（1E + 99，C：C）），< =& G2，B $ 2：INDEX（B： （1E + 99，C：C）），E2）+ 
（C $ 2：INDEX（C：C，MATCH（1E + 99，C：C））< F2）+（C $ 2： 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.}

这篇关于根据日期标准之间另一列中的条件计算唯一文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！