A client requested a column in a table that sums deal amounts of all deal stages except closed won or lost deal stages. I had to create a custom formula field and add it to the table column. But the formula’s output is always zero. Here is the formula I don’t understand why all the values are zero although there are values in the amounts property in the stages that actually does not equal zero
IF(
[DEAL.pipeline] = “Orange 142 Sales Pipeline” AND
NOT (
[DEAL.dealstage] = “Deal Signed / Closed Won(Orange 142 Sales Pipeline )” OR
[DEAL.dealstage] = “Deal Lost(Orange 142 Sales Pipeline )”
),
[DEAL.amount],
0
)
You’re very close with your custom formula, but the reason it’s returning 0 across the board is likely due to an exact match issue in the dealstage values. HubSpot stores internal dealstage values (like appointmentscheduled, closedwon, etc.) - not the label names you see in the UI.
Here’s how to fix it - Use internal values for dealstage instead of display labels:
IF(
[DEAL.pipeline] = “orange_142_sales_pipeline” AND
NOT (
[DEAL.dealstage] = “closedwon” OR
[DEAL.dealstage] = “closedlost”
),
[DEAL.amount],
0
)
How to Find Internal Values?:
Go to Settings > Objects > Deals > Pipelines.
Click “Edit” on your pipeline.
Hover over each stage to find its internal dealstage value.
Same goes for pipeline name — use its internal ID, not the label.
BTW, note - if you’re working with multiple pipelines, it might be better to remove the pipeline condition altogether if you’re only interested in excluding “closed won/lost” deals globally.
Hope this helps!!
If this answer your query, help the community by marking it as a solution.
Hello @PremKannan Thank you for your help, I want to try your suggestion now. I just have a last question i am used to internal names in properties to be values in letters but when i go to deal stages i find only internal ID numbers are those supposed to be the internal value you mean ?
Great follow-up - you’re asking exactly the right question!
Yes, when you’re working with deal stages in formulas or reports in HubSpot, the internal values are actually those alphanumeric ID strings you’re seeing (not the readable names or the typical lowercase text you’d expect from property values).
So to clarify: For properties like Lifecycle Stage or Lead Status, the internal values are usually lowercase text strings (lead, marketingqualifiedlead, etc.). But for deal stages, the internal value is a unique ID, like: a1b2c3d4-e5f6-7890-gh12-ijkl3456mnop
To use them in your formula: You’ll need to plug those ID strings into your custom formula like this:
IF(
[DEAL.pipeline] = “orange_142_sales_pipeline” AND
NOT (
[DEAL.dealstage] = “a1b2c3…wonID” OR
[DEAL.dealstage] = “z9y8x7…lostID”
),
[DEAL.amount],
0
)
Just replace the IDs above with the actual closed won and closed lost stage IDs from your pipeline.
If you ever want to work with readable labels instead, you’d need to create a custom property and sync it with a workflow. But for formulas and reports, always go with those internal IDs.
Hope this helps!!
If this answer your query, help the community by marking it as a solution.
@PremKannan no the internal id that appears to me in deal stages are just digits no strings i used the internal id for pipeline and finally results appeared other than zero but when i use the deal stages condition using their internal numbers i face the same problem all values are zero so we are halfway if it possible to send me screenshot of the location of the internal name of any deal stage it would be great and thank you for your efforts