Circular reference error

**Cjax** · 03-24-2011, 04:54 PM

Hi guys and gals:

This is part of a much larger formula but it's the only part I can't get to work. Can someone tell me what's wrong with it and why it's circular.

IF(RIGHT(B22,5)="Total",SUMPRODUCT(--(Right(C9:C77,5)="Total"),(F9:F77),"Data Not Available")))

Here's the entire formula:

=IF(COUNTIF('Budget Data'!$M$2:$M$53,C22&D22),VLOOKUP(C22&D22,'Budget Data'!$M$2:$P$53,4,0),IF(COUNTIF('Budget Data'!$R$2:$S$13,C22),VLOOKUP(C22,'Budget Data'!$R$2:$S$13,2,0),IF(RIGHT(B22,5)="Total",SUMPRODUCT(--(Right(C9:C77,5)="Total"),(F9:F77),"Data Not Available")))

Thanks!

Cjax

**Cutter** · 03-24-2011, 04:56 PM

You's have to tell us what cell the formula is in.

**Cjax** · 03-24-2011, 05:15 PM

Ah ha! k - the formula is currently in cell F22 but it will run down through F77.

Thanks!

**Cutter** · 03-24-2011, 05:19 PM

You are referring to it in the SUMPRODUCT() portion of your formula.

A formula can not refer to the cell that the formula occupies.

**Cutter** · 03-24-2011, 05:47 PM

BTW - The SUMPRODUCT() formula is also missing its closing bracket.

It should be:

=IF(RIGHT(B22,5)="Total",SUMPRODUCT(--(RIGHT(C9:C77,5)="Total"),(F9:F77)),"Data Not Available")

**Cjax** · 03-24-2011, 10:10 PM

Thanks Cutter - i figured out how to fix the circular reference problem using the same formula. I just stopped summing column F one row above where the formula is active. I locked the F9 in with the $ symbol but left the bottom part of the range open.

Don't know if my explanation makes sense but it worked.

Thanks!