Reputation: 1746
I want to run below formula on all cells, where current value is zero.
Formula on C3
=SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,C$2)
I have tried to put this in another IF statement to validate the cell value first and if that matches condition execute above formula
=IF(C3=0,SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,C$2))
But here I am getting circular reference warning, because the second formula is depends upon same cell value. Is there any other way I can do it ?. mean running the formula if the value is equal to zero on specific cell
Upvotes: 0
Views: 210
Reputation: 33
You can't reference the cell you are working in without creating a circular reference. I usually just add an intermediate column to avoid this. If you want it all in one cell for some reason:
=IF(SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,C$2)) = 0, "Sums are zero", SUMIFS(raw!$D:$D,raw!$A:$A,$A3,raw!$C:$C,C$2)))
Its probably takes twice as long to compute, is ugly, and you gotta keep both formulas the identical if you ever edit it, but it does fit in one cell.
Upvotes: 1
Reputation: 2545
Is this all one worksheet, or multiple? I think the below would work, but can't know without the workbook.
=SUMPRODUCT((C3=0) * (raw!$A:$A = A3) * (raw!$C:$C = C2) * (raw!$D:$D))
Upvotes: 0