多个sumproducts
问题描述:
更好的公式我在范围以下数据“A1:E3”多个sumproducts
1 2 12 25 30
3 13 23 28 31
5 14 17 18 35
我需要找出多少个号码在1到10的每一行范围内下降。例如。在第1行中,有2个数字在1到10范围内,在第2行中只有1个,在第3行中有1个,因此总数是2 + 1 + 1 = 4。同样,我需要找出每行有11到20,21到30和31到40的范围内有多少数字。我写了一个产品配方,并通过改变量程添加了两次。
1 to 10 4
11 to 20 5
21 to 30 4
31 to 40 2
我有上述日期在范围G1:J4其中列J有下面的公式,它被拉下来2行。
=SUMPRODUCT((A$1:E$1>=G1)*(A$1:E$1<=I1))+SUMPRODUCT((A$2:E$2>=G1)*(A$2:E$2<=I1))+SUMPRODUCT((A$3:E$3>=G1)*(A$3:E$3<=I1))
当我的行数增加时,公式变得越来越大。任何人都可以提出一个更好的公式呢?
谢谢
答
答
成立G2中的标准表:I5可以用A1中的数据说:E3
OPTION 1 COUNTIF
在J2使用的公式:
=COUNTIF(A1:E3,"<="&I2)
在J3
抄下使用:
=COUNTIF($A$1:$E$3,"<="&I3)-SUM($J$2:J2)
相反,如果你知道J1会在你的情况下,你可以使用空白以下在J2中,只需根据需要复制下去
=COUNTIF($A$1:$E$3,"<="&I2)-SUM($J$1:J1)
警告:因为我没有做下边界检查,所有小于1的值,即0,-3,0.34都将在1-10范围内计数。此选项是假设做你要找的整数比0
OPTION更大II COUNTIFS
使用COUNTIFS所以设置你的界限为每个测试范围
在J2使用以下抄下:
=COUNTIFS($A$1:$E$3,">="&$G2,$A$1:$E$3,"<="&$I2)
对于每一行你都不需要单独的SUMPRODUCT函数,这可以起作用,并且可以工作(= $ SUMPRODUCT((A $ 1:E $ 3> = G1)*(A $ 1:E $ 3