我正在尝试在 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 ofSUM
,SUMIF
, andSUMIFS
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?
试试这个...
=SUMPRODUCT((A2:A9+B2:B9>0)*(C2:C9+D2:D9<0)*E2:G9)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句