Fairly new to excel, I have a document with rows and columns filled with names. Basically i want to be able to find if any names are there more than 4 times, and if so which names these are. What is the formula I would need to do this? I've tried iterations of countif and index but I just cant seem to get it right. Thanks in advance
这是一个相当新的excel，我有一个文档，其行和列填充了名称。基本上我希望能够找到任何名称是否有超过4次，如果有的话，这些名称是这些。我需要做什么公式呢？我已经尝试过countif和index的迭代，但我似乎无法做到正确。提前致谢
1 个解决方案
#1
1
So, if you have your input values in column A:A. Enter this formula into B1 and drag it down:
因此，如果您在A列中输入了您的输入值：A。在B1中输入此公式并将其向下拖动：
=IF(AND(COUNTIF($A$1:$A1,$A1)=1,COUNTIF($A:$A,$A1)>=4),$A1,"")
And then you get your final result without blank cells in C1 (and down) with this formula:
然后使用以下公式在C1（和向下）中获得没有空白单元格的最终结果：
=IFERROR(INDEX($B$1:$B1000,AGGREGATE(15,6,(ROW($B$1:$B1000)-ROW($B$1)+1)/($B$1:$B1000<>""),ROWS(C$1:C1))),"")
#1
1
So, if you have your input values in column A:A. Enter this formula into B1 and drag it down:
因此，如果您在A列中输入了您的输入值：A。在B1中输入此公式并将其向下拖动：
=IF(AND(COUNTIF($A$1:$A1,$A1)=1,COUNTIF($A:$A,$A1)>=4),$A1,"")
And then you get your final result without blank cells in C1 (and down) with this formula:
然后使用以下公式在C1（和向下）中获得没有空白单元格的最终结果：
=IFERROR(INDEX($B$1:$B1000,AGGREGATE(15,6,(ROW($B$1:$B1000)-ROW($B$1)+1)/($B$1:$B1000<>""),ROWS(C$1:C1))),"")