# Sum(Collect Calculation giving #Circular Reference Error

✭✭✭✭

=SUM(COLLECT({Forecast Amount}, {Customer}, \$Label@row, {Sales Stage}, Qualification\$1))

That is the calculation I am using bring forecast sales based on Customer and Sales Stage into a metrics collection sheet. I am going out to the Sales Pipeline sheet for the information, two (2) sheet prints attached for reference.

This is the actual metric collection sheet

This is the Sales Pipeline, some fields are hidden simply to provide better viewing.

I build these metrics all the time, but not with the sum function only with the count function. So what am I missing?

Tags:

• ✭✭✭✭✭✭
edited 08/31/24

The following formula works.

=SUM(COLLECT({Forecast Amount}, {Customer}, Label@row, {Sales Stage}, "Qualification"))

I could recreate your #CIRCULAR REFERENCE error by referencing Qualification\$1 in the first row.

If you want to somehow put "Qualification" at the first row, make the formula a cell formula like the one in [Qualificatin 1].

The correct approach is simply putting the "Qualification" text as the COLLECT condition.

[Qualification] =SUM(COLLECT({Forecast Amount}, {Customer}, Label@row, {Sales Stage}, Qualification\$1))

[Qualification 1] =SUM(COLLECT({Forecast Amount}, {Customer}, Label@row, {Sales Stage}, [Qualification 1]\$1))

[Qualification 2]=SUM(COLLECT({Forecast Amount}, {Customer}, Label@row, {Sales Stage}, "Qualification"))

[Discovery]=SUM(COLLECT({Forecast Amount}, {Customer}, Label@row, {Sales Stage}, "Discovery"))

Sample Sales Pipeline Data

• ✭✭✭✭

Perfect, thank you!

• ✭✭✭✭✭✭

Happy to help!😁

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!