Allow Multiple Conditions for Calculated Properties of Type Count / Sum

[basil]

It is great that we can count or sum over objects using calculated properties. However, we often have cases where we want to put not just one but multiple conditions. So if we could have filter functionality similar to the normal list filters or workflow filters where we can combine multiple conditions with AND / OR that would make calculated fields much more powerful for us.

[basil]

Also we often need conditions choosing multiple labels and currently we can only select one single label.

[MatP]

Hi, 

I second this idea, this would also be a useful addition for our internal use cases.

Thank you, 

MatP

[ParkerShort]

We're looking to use this to calculate revenue of certain types of deals, either based off of product type or pipeline. For instance, if we want to track all closed won deals in a specific pipeline, we need two criteria, but that's currently not possible (there are some workarounds, but they're ugly).

[yasiooy]

Me and my team would also love to have that option, we have multiple clients that could benefit from it.

[SarahMayo]

Please implement this! 🙂

[Hawk-Steve]

+1 from me, would be a great improvement on what currently exists.

A current use case:

• I need a tally of how many associated deals a contact has within 2 stages on a sales pipeline.

[LindsayMB]

We also need something like =SUM((%-%)*whole number). Is there a workaround and I'm just not seeing it?

[joseph_cone]

please Hubspot, calculated fields would be SUPER useful if we could add more criteria. 

- Use case: need to sum associated deals closed in a timeframe/year, but ONLY if the association (label) is X

[SKripp]

Need this!!!!!

[CColegrove]

Found myself here after trying to do this myself. We have multiple Resource Types with their own associated costs for our services. Trying to do: 

That will then feed into an above cost of whatever that role equals. 

[GrantCarlile]

Agreed, this would be great.

[PVignon]

We need this 

[Viddy]

hi team, posting on behalf of my customer, they are also looking to be able to add more than one condition for each calculation property because at present they are creating separate calculation properties for each condition and it is not a sustainable long term approach for them. 

[Bob2245]

While adding multiple conditions is handy, there's a fairly easy workaround for this that will solve most cases. In this example, I want to calculate the revenue over 2021 in a Company calculated property, so I need to sum the amount of associated deals, where close date is inside 2021 and stage is closed won. This goes as follows:

1. Create a Single line text property. Name it whatever you want. This property will only act as a condition property and won't be visible for regular users. I like to label properties like this with the affix "SYSTEM - " to scare off any non-tech users. In my example I used "SYSTEM - Revenue year"
   
   
2. Create a workflow with a trigger for each of the criteria you want to use. For instance:
   1. Deal stage = Closed Won
      AND
   2. Close date is between 01-01-2021 and 31-12-2021
      
      
3. Use an action step to set the filter property from step 1 to a value that you will use in your condition. In this case, I use "2021"
   
   
4. Now create your calculated property, in this case a sum of associated deal amounts, and set the condition to be  "your filter property == value from step 3". In my example "SYSTEM - Revenue Year == 2021"
   
   
5. And presto! Your deal amounts will be summed nicely (or whatever your use case is), but only if they meet your multiple conditions 🙂

[BrunAlbuquerque]

Adding my request as well.

I created a calculation property, but actually couldn't achieve what I was looking for. So I'd like to receive a help please!

- I'm setting targets for the sales team - they have kind of a multiplier for their targets depending on the lenght of the contract and ARR. 

So it's like:

1 year contract = ARR

2 year contract = 1,22 *ARR

3 year contract = 1,33*ARR

Is it possible to create a calculated property with 3 different conditions? I kind new at Hubspot, used to work with SFDC, so struggling a bit. 

Thanks!

[basil]

We would also need this

[smcmillian]

Howdy, 

I'm a HubSpot reporting PM and noticed this was a highly requested feature set for calculated properties. It seems like the goal of this thread is to apply one or more conditions to a a set of data and then create either a sum or a count based on the conditional filters. This can be achieved through datasets + reporting today -- pictures attached. I'm curious if the pictured reporting solution solves the user problem in this thread or is there a next step that requires this information to be saved in a property instead of a report? If yes, I would love to hear what that next operational step is and how you're using this aggregated data next, because our team is actively thinking thinking about this connection point.



Conditional Logic using if() function in datasetsReporting enabling the ability to create a summation or a count

[ParkerShort]

@smcmillian Thanks for chiming in. I'd love to see the formula feature from datasets replace the calculated properties feature, if that's what you're proposing. It's much more powerful and opens up a ton more options. As long as it's available in all the same places as a calculated property, that would definitely work.

[abrow]

@smcmillian I wouldn't think reporting would allow for workflow automation. We want robust calculated fields so that we can build workflows off of them. We also connect HubSpot data to other systems and the reporting feature wouldn't help in that case either.

[Hawk-Steve]

Good thoughts on this @smcmillian.

I have quite a lot of automation that run off of calculated properties. Such as triggering alerts when a contact record is associated with more than 5 open tickets in a specific pipeline. The calculated property would need to have 2 conditions - Pipeline AND open status.

To do this in today's portal I instead have to create additional properties, workflows, etc to create a single property that can be used in a condition.

The other thing to note is that calculated properties are available to certain Hubs and grades, datasets require the purchase of opshub enterprise. It's a bit of a hard sell when a complicated workaround comes at no additional cost (If we're not talking sanity or system health 😉)