Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula needed

Options
amy16451
amy16451
edited 12/09/19 in Archived 2017 Posts

Happy Thursday, all!  

I have a sheet with invoice dollar amounts in several cells.('Amount')

Next to these cells, I have a checkbox column called 'Paid' that is checked on if the invoice was paid, checked off if the invoice was not paid.

I need to total all invoice amount cells for invoices that are paid.

Any tips on the formula that should be used for to complete this?  FYI, the TOTAL INVOICED amount that is showing(in the screenshot) at this time was generated by me selecting the proper fields.  My intention is to automate formula and make the 'Paid' field a function of it.

Capture.PNG

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    =sumif([Paid]:[Paid], 1, [Amount]:[Amount]

    That formula should do the trick. checkboxes are 1 if checked and 0 if not. Basically the formula is looking at the range of the paid column and anywhere there is a 1 it is summing the amount. 

  • amy16451
    Options

    Thank you, Mike!  I've got this formula in, but I'm getting #BLOCKED

    =sumif([Paid]:[Paid], 1, [Amount]:[Amount]

    Is the formula that is typed into the cell.

    I recognize that #BLOCKED means that one of the cells has an error.  There are some cells that have paid unchecked.  Do I have to account for that in the formula.(Note lower entries in the screenshot)

    Any thoughts?

     

     

    Capture.PNG

  • amy16451
    Options

    Mike - I figured it out!  I had other things lower in that column so I needed to limit the rows.  Thank you so very much!!  I'm new to formulas so every bit of help counts!

    If you live near Maryland, I owe you lunch!!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Haha. Glad you figured it out! I am on the west coast. So, no go. Enjoy! 

This discussion has been closed.