具有条件的唯一/唯一计数 [英] Distinct/Unique count with criteria
问题描述
您好，我正在尝试使用与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.
这篇关于具有条件的唯一/唯一计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
