SUMPRODUCT and circular reference help!

[khadert]

Hi,

This is driving me crazy and it would be greatly appreciated if someone could help me out.

I want to sum the values in column M where the corresponding value in column F = 6 and the value in column E = E33. The formula I have come up with is below:

=SUMPRODUCT(($F$1:F5000=6)*($E$1:E5000=E30),$M$1:M5000)

However since I want the answer pasted in column M, this gives me a circular reference. Note that I want the answer to be copied into cell M30 and that F30 in is not equal to 6.

To avoid this I tried the formula

=SUM(SUMPRODUCT(($E$1:E29=E30)*($F$1:F29=6),$M$1:M29),SUMPRODUCT((E31:$E$5000=E30)*(F31:$F$5000=6),M31:$M$5000))

This works but the problem is if I try to paste it into another cell in column M, say M45 (again the value of F45 is not equal to 6) I get a circular reference problem again (between cells M45 and M30).

Is there a way I can resolve this? i.e. i need excel to only run the sum product on the cells within column M where the sumproduct conditions have been met and ignore the others thereby avoiding a circular reference.

I hope that makes sense!

 

[khadert]

Sorry I forgot to add that I am using a work computer and our administrators have protected the "preferences" tabs so I dont have the option of simply allowing the circular references. I therefore need to modify the equation somehow (or potentially write a simple macro though this is not my preferred option).