or Explore Discussions

# How do I correctly use Sum & Value formulas together?

11/25/21
Accepted

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?

Tags:

• ✭✭✭

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.

• ✭✭✭

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.

• 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. :)