Summary Sheet Formula Referencing a Summary Sheet Dropdown Field

Kathy Tilley

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

Katie G

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]#)

Katie G

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")

Mike TV

@Kathy Tilley

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]#)

Katie G

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]#)

Katie G

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")

Kathy Tilley

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