Circular reference workaround

[familiarstranger]

Hello all,

Longtime lurker (these threads have helped me numerous times before) first time poster. I am running into a circular reference with this formula and need some insight as to how to structure it better. Here is the formula:

=IF(B1="Total",SUMIFS(C:C,A:A,A1),IF(LEN(A1)<3,"",COUNTIFS(Data!F:F,Jan!B1,Data!A:A,"<2/1/2014")))

The Sumifs is what is giving me trouble. A contains store locations, B the store reps, C this formula (which gives the sales total for each rep) at the end of each list of locations is a rep named "Total" on this line I would like to sum all the sales figures for the respective store.

Thank you in advance!

 

[Gilliam]

correct me if I am wrong, the formula you use above is placed in column C?

 

[familiarstranger]

correct me if I am wrong, the formula you use above is placed in column C?

You are correct

 

[Gilliam]

You are correct

Then u cant use the entire column C in the formula, have it use every cell above it.