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?

Best Answer

  • Devin LeeDevin Lee ✭✭✭
    Answer ✓

    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.

Answers

  • Devin LeeDevin Lee ✭✭✭
    Answer ✓

    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.

  • 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 or Register to comment.