源文件 计算表
您好,我正在尝试使用与B列-制造商(导入ID)相匹配的条件来计算源文件产品(导入ID)的C列的唯一计数.谷歌搜索并使用以下公式,但未返回结果.任何人都可以帮助我哪里出了问题??
Hi, I'm trying to calculate the unique counts of column C of my source file Product (Import ID) with criteria it matches column B - Manufacturer (Import ID). Googled around and used below formula but returned no results. Anyone could help me where went wrong ??
= SUM(IF(FREQUENCY('Report 1'!C:C,'Report 1'!C:C)> 0,(''Report 1'!B:B ='mapped stat'!B55)* 1 ,0))
=SUM(IF(FREQUENCY('Report 1'!C:C,'Report 1'!C:C)>0,('Report 1'!B:B='mapped stat'!B55)*1,0))
"Report1"列C是我要计算的唯一计数范围& Report1 B列是我要与计算表(mappedstat)匹配的条件/条件
Where Report1 column C is the unique count range I'm trying to count & Report1 column B is the condition/criteria I'm trying to match with my calculation sheet (mappedstat)
推荐答案有一个使用SUMPRODUCT和COUNTIF函数的标准COUNTUNIQUE方法. (例如SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12&")))).可以通过更改为COUNTIFS函数将其扩展为包括条件,但必须注意确保没有#DIV/0!错误可能会发生.
There is a standard COUNTUNIQUE method using SUMPRODUCT and COUNTIF functions; (e.g. SUMPRODUCT(1/COUNTIF(A2:A12, A2:A12&"")) ). This can be expanded to include conditions by changing to a COUNTIFS function but care must be made to ensure that no #DIV/0! error can occur.
=SUMPRODUCT(('R1'!B1:B12=B55)/(COUNTIFS('R1'!C1:C12,'R1'!C1:C12&"",'R1'!B1:B12, B55)+('R1'!B1:B12<>B55)))避免使用完整的列范围引用.
Avoid full column range references.
更多推荐
具有条件的唯一/唯一计数
发布评论