Excel SUM 条件

威尔巴特尔

我正在尝试在 Excel 中完成一个中等复杂的 SUM 函数,但我没有运气。

有关视觉说明,请参阅所附图像。

电子表格

目标是仅对符合条件的行中的 E、F 和 G 列求和。标准是 A 和 B 列的总和必须大于零,C 和 D 列的总和必须小于零。E+F+G之和就是A+B与C+D之差

IF(AND(SUM(A2:B2)>0,SUM(C2:D2)<0),SUM(E2:G2),0) is the formula I use in each row of the I column. I want to consolidate all rows into one formula because I will probably have close to 100 rows that need to compute to 1 cell. An easy alternative would be to keep my placeholder column (I) but it is not an option in my case. I have tried combinations of SUM, SUMIF, and SUMIFS but am having a hard time.

I tried SUMIF because the [sum_range] parameter should've let me do what I want but every time it either read zero of #VALUE. I readup on the issue and it seems a workaround exists using arrays. I tried implementing the array as suggested by Microsoft but I always get a value of zero. I really don't want to have to use a macro for this. It seems so simple but after 5 hours of no progress I've learned otherwise. Any ideas?

微软支持页面

Subodh Tiwari sktneer

试试这个...

=SUMPRODUCT((A2:A9+B2:B9>0)*(C2:C9+D2:D9<0)*E2:G9)

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章