人生若精通excel，何至秋风悲画屏

[TIFF]

请问一个excel的问题

用下面这个简单的table做一个例子。如果我想count有多少个region 有item1 和item2， 正确答案是，有item1 的region有两个，US and Canada， 有item2的也是一样。

简单的table容易看，但是如果数据量大，只能依靠function来数。用我已知的count function， 会数重复，会说有item1 的有3个region， 有item2的有4个region。 请问怎么样才能让count不去重复数同样的region？就是要在前面加个判断，如果都是US，只数一次，都是Canada，也只数一次。谁知道啊？先谢谢了。

 

[9981]

what is granularity of the table then? what are the difference of 3 US items or of 3 Canada items? It causes readability problem.

 

[idolou]

COUNTIF(A2:A8,"US")

 

[TIFF]

COUNTIF(A2:A8,"US")

I need a non-hard coded function that can apply to item 1 or item 2 column.

 

[TIFF]

what is granularity of the table then? what are the difference of 3 US items or of 3 Canada items? It causes readability problem.

The 3 different US items are categorized by other criteria. I need to know if I can set a condition in my count function to identify duplicated records in reference columns. In this case, the reference column is the region column and the records are US and Canada.

 

[冷笑9声]

谴责一下技术坑。

 

[9981]

in sql, it is always count distinct

or different pivot table views?

 

[CatLostHerFish]

没仔细看，如果比较复杂的运算，用VBA编个小function就是了。

 

[TIFF]

in sql, it is always count distinct

没仔细看，如果比较复杂的运算，用VBA编个小function就是了。

I am not a coding lady.

 

[atla]

这个要顶，楼主举一反三嘛

 

[atla]

I am not a coding lady.

啥？ coding man ？

 

[TIFF]

啥？ coding man ？

Maybe. 干锅做好了嘛？等我把这个搞定了，去找你一起吃去啊。

 

[idolou]

try this:
=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

 

[TIFF]

try this:
=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

蘑菇，what is this？

 

[idolou]

蘑菇，what is this？

你会用excel吗？把这个贴在item1 那行的末尾， 试试。