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.
Thanks.
Kathy Tilley
Best Answers

For the first question, I think this might be the easiest way to do it:
=SUMIFS([Potential Revenue Opportunity ($)]:[Potential Revenue Opportunity ($)], [Likelihood to Convert]:[Likelihood to Convert], <>"Converted", [Relationship Manager]:[Relationship Manager], [Relationship Manager]#)

For the second one, the way you have it, first it is finding all of the ones that match the relationship manager (regardless of if they converted or not), then it is subtracting from that all of the ones that have converted (even if they are a different relationship manager).
I think instead you may want to do a COUNTIFS with both criteria, such as:
=COUNTIFS([Relationship Manager]:[Relationship Manager], [Relationship Manager]#, [Likelihood to Convert]:[Likelihood to Convert], <>"Converted")
Answers

The first formula has incorrect syntax in it. This should be it fixed (removing the "@cell =" part):
=SUM([Potential Revenue Opportunity ($)]:[Potential Revenue Opportunity ($)]  SUMIFS(([Potential Revenue Opportunity ($)]:[Potential Revenue Opportunity ($)], [Likelihood to Convert]:[Likelihood to Convert], "Converted")), [Relationship Manager]#)

For the first question, I think this might be the easiest way to do it:
=SUMIFS([Potential Revenue Opportunity ($)]:[Potential Revenue Opportunity ($)], [Likelihood to Convert]:[Likelihood to Convert], <>"Converted", [Relationship Manager]:[Relationship Manager], [Relationship Manager]#)

For the second one, the way you have it, first it is finding all of the ones that match the relationship manager (regardless of if they converted or not), then it is subtracting from that all of the ones that have converted (even if they are a different relationship manager).
I think instead you may want to do a COUNTIFS with both criteria, such as:
=COUNTIFS([Relationship Manager]:[Relationship Manager], [Relationship Manager]#, [Likelihood to Convert]:[Likelihood to Convert], <>"Converted")

Thank you @Mike TV and @Katie G!
Unfortunately Mike's formula for the first question still gave me an UNPARSEABLE error but both of Katie's formulas worked :).
I much appreciate you both taking the time to respond to me. I'm a fairly new Smartsheet user and still figuring out this formula stuff  which often has me stumped 😫
I hope you both have a wonderful day.
Warmly,
Kathy Tilley
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!