保加利亚炮王 发表于 2021-10-26 17:00:00

统计


如图所示，数据里面按照项目号区分有重复的部分，现在只想统计出不重复的发票款。哪位高手帮忙一下。

慕容弘博 发表于 2021-11-27 04:53:25


=SUMPRODUCT((1/COUNTIFS($A$2:$A$8,$A$2:$A$8,$B$2:$B$8,$B$2:$B$8))*($A$2:$A$8=I2)*($E$2:$E$8))

寐得嘞 发表于 2021-12-9 10:44:47

=SUMPRODUCT((E$2:E$8)*(A$2:A$8=I2)/COUNTIFS(A$2:A$8,A$2:A$8,B$2:B$8,B$2:B$8))

老抽 发表于 2021-12-18 02:38:38

=SUMPRODUCT((MATCH(A$2:A$8&B$2:B$8,A$2:A$8&B$2:B$8,)=ROW($1:$7))*E$2:E$8*(A$2:A$8=I2))

杨头晕 发表于 2022-1-10 02:09:36

数组公式：=SUM(IFERROR((MATCH(1&I2&B$2:B$8&E$2:E$8,COUNTIF(I2,A$2:A$8)&A$2:A$8&B$2:B$8&E$2:E$8,0)=ROW($2:$8)-1)*(E$2:E$8),0))
页: [1]
查看完整版本: 统计