问题描述
我需要的是根据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）））
但它显示零值。这可能是因为日期条件需要两个条件，一列。
请使用此公式或任何其他方法根据此条件计算唯一的值。日期
基于
现在公式很难维护，对行数有不断的添加和删除。要创建动态范围，我们可以每个完整的列，并使用将最后一个日期定位为每列的终止符。
= 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函数处理的行，以便计算效率是非常重要的。
¹
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.
¹
这篇关于根据日期标准之间另一列中的条件计算唯一文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！