统计符合行和列条件的数据数量

lms8650

jisijie630 发表于 2022-8-16 22:34
=SUMPRODUCT(($B$2:$B$19=$B25)*($C$2:$BJ$19>30)*(SUBSTITUTE($C$1:$BJ$1,CHAR(13),)=$C25&IF(MOD(COLUMN( ...

感谢大神！“数量”汇总（求和）的时候不需要满足“>30”这个条件

minchar

D25=COUNTIFS($B$1:$B$19,B25,OFFSET($B$1,,MATCH(C25&D$24,CLEAN($C$1:$BJ$1),),19,1),">30")
E25=SUMIF($B$1:$B$19,B25,OFFSET($B$1,,MATCH(C25&E$24,CLEAN($C$1:$BJ$1),),19,1))
数组下拉

w4275

1. =SUMPRODUCT(($B25=$B$2:$B$19)*(OFFSET($B$2:$B$19,,MATCH($C25&D$24,CLEAN($C$1:$BJ$1),))>30))

复制代码

1. =SUMPRODUCT(($B25=$B$2:$B$19)*OFFSET($B$2:$B$19,,MATCH($C25&E$24,CLEAN($C$1:$BJ$1),)))

复制代码

jisijie630

lms8650 发表于 2022-8-16 23:00
感谢大神！“数量”汇总（求和）的时候不需要满足“>30”这个条件

=SUMPRODUCT(($B$2:$B$19=$B25)*($C$2:$BJ$19>(COLUMN(A1)=1)*30)*(SUBSTITUTE($C$1:$BJ$1,CHAR(13),)=$C25&IF(MOD(COLUMN(A1),2),"平均","数量"))*IFERROR($C$2:$BJ$19^(COLUMN(A1)>1),))
下拉右拉。

lms8650

minchar 发表于 2022-8-17 06:08
D25=COUNTIFS($B$1:$B$19,B25,OFFSET($B$1,,MATCH(C25&D$24,CLEAN($C$1:$BJ$1),),19,1),">30")
E25=SUMIF( ...

非常感谢您的方案

lms8650

jisijie630 发表于 2022-8-17 07:33
=SUMPRODUCT(($B$2:$B$19=$B25)*($C$2:$BJ$19>(COLUMN(A1)=1)*30)*(SUBSTITUTE($C$1:$BJ$1,CHAR(13),)=$C ...

非常感谢！需要消化一下!

lms8650

minchar 发表于 2022-8-17 06:08
D25=COUNTIFS($B$1:$B$19,B25,OFFSET($B$1,,MATCH(C25&D$24,CLEAN($C$1:$BJ$1),),19,1),">30")
E25=SUMIF( ...

非常感谢您的回答

alan57

平均

1. =SUM(($C$2:$BJ$19>30)*($B$2:$B$19=B25)*(LEFT($C$1:$BJ$1,10)=C25)*(RIGHT($C$1:$BJ$1,2)=$D$24))

复制代码

数量

1. =SUM(($C$2:$BJ$19)*($B$2:$B$19=B25)*(LEFT($C$1:$BJ$1,10)=C25)*(RIGHT($C$1:$BJ$1,2)=$E$24))

复制代码

lms8650

alan57 发表于 2022-8-17 11:38
平均

数量

感谢，已经用采用了13楼的公式