Summary Sheet Formula Referencing a Summary Sheet Dropdown Field
Good Afternoon - I need some help with a couple of Summary Sheet formulas.
I've set up a summary sheet which includes a summary field drop down list of Relationship Managers. I want to set up other summary fields to calculate based on the name chosen in the Relationship Manager summary field but having challenges with the formulas.
What calculating the "$ Value of Prospects by RM", I need to subtract any prospects who have "Converted" in the "Likelihood to Convert" column of my Smartsheet. Here is the formula I've come up with but that is giving me the #UNPARSEABLE error:
=SUM([Potential Revenue Opportunity ($)]:[Potential Revenue Opportunity ($)] - SUMIFS(([Potential Revenue Opportunity ($)]:[Potential Revenue Opportunity ($)], [Likelihood to Convert]:[Likelihood to Convert], @cell = "Converted")), [Relationship Manager]#)
Second question - although it looks like there are no errors in the formula in the "# of Prospects by RM" field and the number does update when the name in the Relationship Manager field is changed - it's not working correctly.😒. It's always 1 less than what it should be and if I choose a name that has no prospects assigned to them in the Smartsheet the # of Prospects by RM comes up with -1. Once again for this field, I need to subtract prospects marked "converted". Here is the formula I've entered:
=COUNTIF([Relationship Manager]:[Relationship Manager], [Relationship Manager]#) - COUNTIF([Likelihood to Convert]:[Likelihood to Convert], ="Converted")
What am I doing wrong? I'm obviously missing something here - LOL.
Help Article Resources
Check out the Formula Handbook template!