excel中比较2列后计算重复的次数

显示全部楼层 · 2008-7-5 15:11:59

我可以很容易地用＝countif(a:a,a1)来求出在一列中，某个数值的重复的次数，但是如下面的数据，如何编函数来求出a列中是week 1同时在b列中又是apple的重复的次数？
a
b
week 1 apple
week 2 apple
week 3 apple
week 4 banana
week 1 banana
week 2 banana
week 3 banana
week 4 banana
week 1 apple
week 2 apple
week 3 apple
week 4 orange
week 3 orange
week 4 orange
week 1 orange
week 1 orange
week 1 orange
week 4 orange
week 1 orange
week 2 orange
week 4 apple
week 1 apple
week 2 apple
week 3 banana

千问 · 2008-7-5 15:11:59

=sumproduct(($A$1:$A$100=A1)*($B$1:$B$100=B1))A1中为week 1 , B1中为apple向复制公式,可以求它多条件的统计sumproduct不能引用整列

千问 · 2008-7-5 15:11:59

看excel的帮助文档，统计里边可以这样写=countifs(a:a,a1,b:b,b1)

千问 · 2008-7-5 15:11:59

输入公式=sumproduct((A:A=A1)*(B:B=B1))