How do I correctly use Sum & Value formulas together?

I'm trying to get the total monthly parking charges for my office.

I have a column showing each individual's monthly parking charges, if applicable (enabled or disabled).

For the sheet summary, using =sum([Monthly Cost]1:[Monthly Cost]84) doesn't return anything but 0 because the column is using text.

I've tried adding the VALUE function but keep getting errors--the formula works on a single cell but not if I try dragging more than one.

Any suggestions or help?

Find more posts tagged with

Sheet Summary

Accepted answers

Devin Lee

Hey @PAS at Vista

Remove the quotations around the 299 so it reads like below. Also remove the numbers from the Estimated Monthly Cost so it will grab all future items in that row.

=IF([Parking Enabled?]@row="Enabled",299,"")

=SUM([Monthly Cost]:[Monthly Cost]

If you are feeling extra spicy you can move the 299 into the sheet summary as Parking Cost and swap out the 299 with [Parking Cost]#

Don't forget to apply the currency format the Cost columns as well.

All comments

Devin Lee

Hey @PAS at Vista

Remove the quotations around the 299 so it reads like below. Also remove the numbers from the Estimated Monthly Cost so it will grab all future items in that row.

=IF([Parking Enabled?]@row="Enabled",299,"")

=SUM([Monthly Cost]:[Monthly Cost]

If you are feeling extra spicy you can move the 299 into the sheet summary as Parking Cost and swap out the 299 with [Parking Cost]#

Don't forget to apply the currency format the Cost columns as well.

PAS at Vista

Devin,

Thank you sooooooo much!!

I spent so much time trying to do this by myself by just staring and trying and trying---your help totally worked!

Can't wait to wow em at work with this. :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.