我正在寻找一个公式来计算特定列组合的数量。我正在寻找数据的示例与此类似。
I'm looking to build a formula that counts the number of specific combinations of columns. An example of what I'm looking at for data is similar to this.
Invoice Account A1415 1234 A1415 4567 B1415 1234 B1415 1234 B1415 4567 A1415 4567 C1415 1234数据我从列中有5种独特的组合。任何人都可以帮助建立同时查看两列的countif方程吗?我对此很困惑。谢谢!
Out of this data I have 5 unique combinations from the columns. Can anyone help build a countif equation that looks at both columns together? I'm stumped on this one. Thanks!
推荐答案数组公式** ,假设数据在A1:B7中:
Array formula**, assuming data in A1:B7:
= SUM(IF(FREQUENCY(MATCH(A1:A7& |&B1:B7,A1:A7& |&B1: B7,0),ROW(A1:A7)-MIN(ROW(A1:A7))+ 1),1))
注意
****数组公式的输入方式与标准公式的输入方式不同。您不仅要按ENTER键,还需要先按住CTRL和SHIFT键,然后再按ENTER键。如果操作正确,您会注意到Excel在公式周围使用了大括号{}(尽管不要尝试自己手动插入大括号)。
更多推荐
excel计算列的唯一组合
发布评论