Hello, I’m trying to create a custom formula based on recent form conversion. I’d like the formula to count any form with “ent |” in the title while also excluding any form with “phd” in the title. I’ve come up with this formula but the “and not” section is not working. Does anyone know how to use “and not” in the custom formulas?
Thanks!
COUNT(IF(AND(CONTAINS([CONTACT.recent_conversion_event_name], “ENT |”), and (NOT(CONTAINS([CONTACT.recent_conversion_event_name], "phd”))), [CONTACT.recent_conversion_event_name], NULL))
Hi @JGrayson6,
Could you please first confirm where exactly in HubSpot you’re entering this? Is this a custom report builder formula field, within a dataset, or a calculation property?
If it’s a report, which data sources do you have in it? Which object do you want to aggregate this counted information on?
Are you aware that “Recent conversion” does not hold all form submissions, and just the most recent one, ignoring all previous submissions?
Best regards
Hi Karsten,
I’m entering the formula in a custom formula field within reporting.
I’m using contact data as my data source. I’m trying to aggregate the type of their most recent form conversion. And yes, I am only interested in the most recent form conversion.
Thanks for your help!
Jenny
@JGrayson6 custom formula fields can only perform row level math, not column level math. Counting would at best return 1 or 0 for each contact.
What exactly do you want to count per contact? What should the result of the formula per contact be? For recent conversion, whatever you count can’t be more than 1.
I’m trying to aggregate the number of contacts that filled out a form with a certain word in the name, and I want to exclude contacts that filled out a form if it has certain words in the name.
Let’s say I have a form titled “Enterprise | Math Contact Us Form” - I’m setting up formulas to count the contacts that filled out a form if their form conversion contains the word “math” and another formula that counts contacts that filled out a form with the word “enterprise” but since this form includes both words, I’d like the contacts to only fall in one bucket: math or enterprise. So I’m hoping to create a formula to say "if the form conversion contains ‘enterprise’ AND NOT ‘math,’ count them in the enterprise bucket. Does that help? Is it possible?
thanks!
@JGrayson6 so it’s important to understand that a custom formula field in the custom report builder cannot in itself count how many contacts meet its criteria. It’s not a filter, it just performs the formula and returns a result for each individual contact.
If that’s what you want to do, the formula would be:
IF(CONTAINS([CONTACT.recent_conversion_event_name],“Math”),true,false)
This is a field that would return, for each contact, either true or false.
You can then visualize how many contacts have a true value, how many a false value - for this particular field.
You can then repeat the process for other keywords.
If you want a field that returns a number, e.g. 10 for math, 50 for enterprise, that’s not now formula fields work - as that would be column-level math, not row-level math.
If you want to exclude a keyword, it would look like this:
IF(CONTAINS([CONTACT.recent_conversion_event_name],“Math”),true,false) AND NOT(IF(CONTAINS([CONTACT.recent_conversion_event_name],“Enterprise”),true,false))
Interesting. I’ve created custom formulas in the past that counts each contact which fits a criteria as “1” and then returns a sum of the “1’s.” I wonder how this is different (this is rhetorical and does not require a response from you). Thanks for all your help today.
@JGrayson6 returning a 0 or 1 per contact is still row-level math - per row, one value is returned. Yes, the report can then aggregate it, but a custom formula field cannot directly look into the entire column.
I’ve updated my earlier reply to include the keyword exclusion formula.
Thank you, Karsten! The solution is very helpful. I’ll try it today.