Sum(Collect Calculation giving #Circular Reference Error

LDLValentine

=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?

jmyzk_cloudsmart_jp

Hi @LDLValentine

The following formula works.

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

https://app.smartsheet.com/b/publish?EQBCT=7c4a20e4240d4c64bc564f7167997714 (Link to published demo sheet)

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

https://app.smartsheet.com/b/publish?EQBCT=9129cf4359f74b09b6b2ac069e799989

LDLValentine

Perfect, thank you!

jmyzk_cloudsmart_jp

Happy to help!😁