Formula Help

Graham Cracker

Need help with this formula:

I have a YES/NO category and if the answer is YES then I want to add all the columns listed below, if the answer is NO then I want the column to show $0.

=IF([Exterior Signage (YES/NO)]@row, YES, Exterior Survey Cost]@row + [Door Vinyl Cost]@row + [Privacy Screen Cost]@row + [Illuminated Channel Letters Cost]@row + [NON-Illuminated Channel Letters Cost]@row + [Monument Sign Cost]@row + [Post & Panel Cost]@row + [Parking Signs Cost]@row + 4025, IF([Exterior Signage (YES/NO)]@row, NO = 0)

Mark.poole

=IF([Exterior Signage (YES/NO)]@row = "YES", ([Exterior Survey Cost]@row + [Door Vinyl Cost]@row + [Privacy Screen Cost]@row + [Illuminated Channel Letters Cost]@row + [NON-Illuminated Channel Letters Cost]@row + [Monument Sign Cost]@row + [Post & Panel Cost]@row + [Parking Signs Cost]@row + 4025), "$0")

David Tutwiler

You are already asking the IF question at the beginning, so you don't need to restate it. I think this should do the trick.

=IF([Exterior Signage (YES/NO)]@row = "YES", Exterior Survey Cost]@row + [Door Vinyl Cost]@row + [Privacy Screen Cost]@row + [Illuminated Channel Letters Cost]@row + [NON-Illuminated Channel Letters Cost]@row + [Monument Sign Cost]@row + [Post & Panel Cost]@row + [Parking Signs Cost]@row + 4025, 0)

Mark.poole

you are almost there @Graham Cracker

=IF([Exterior Signage (YES/NO)]@row= "Yes",Sum(Exterior Survey Cost]@row,[Door Vinyl Cost]@row,[Privacy Screen Cost]@row,[Illuminated Channel Letters Cost]@row,[NON-Illuminated Channel Letters Cost]@row,[Monument Sign Cost]@row,[Post & Panel Cost]@row,[Parking Signs Cost]@row) + 4025,If([Exterior Signage (YES/NO)]@row= "NO",0))

Mark.poole

Also if all those Columns are right next to each other you could do Sum([First Column on left]@row:[Last Column from left])

Mark.poole

https://community.smartsheet.com/discussion/comment/435178#Comment_435178

That is Very true. How ever if they do not have a yes or a no in the column it will also show a 0. If he wants it to remain blank until there is a yes or no then They would need the stacked If statements.

Graham Cracker

Hi David and Mark thank-you both for your quick responses. Unfortunately I keep getting the #UNPARSEABLE error with the formulas above. Any thoughts?

Mark.poole

Are there any Entries that are not a numerical value?

Graham Cracker

Nope. All are numbers.

Mark.poole

would you mind sharing an image of what your working with?

Graham Cracker

Can I share the sheet with you?

Mark.poole

You can if you like. How ever if there is sensitive data on it I would not recommend sharing it with some one outside of your organization.

Graham Cracker

I can remove the sensitive info. What is your email?

Mark.poole

Mark.poole

do you have Microsoft teams?

Mark.poole

=IF([Exterior Signage (YES/NO)]@row = "YES", ([Exterior Survey Cost]@row + [Door Vinyl Cost]@row + [Privacy Screen Cost]@row + [Illuminated Channel Letters Cost]@row + [NON-Illuminated Channel Letters Cost]@row + [Monument Sign Cost]@row + [Post & Panel Cost]@row + [Parking Signs Cost]@row + 4025), "$0")