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.

how to sum the column amounts, if the status of each raw is Green and that is before end of may

Zafar Yuldashev
edited 12/09/19 in Archived 2016 Posts

trying to establish a quick summary report for Senior Management. want to sum the amounts in the contracts column, if the status of raw items are green and the date is happening before end of may. highly appreciate your guidance. 

Comments

  • Hi Zafar-- I think you could accomplish this using a SUMIFS formula. We don't currently support conditional statements within a SUMIF statement, though, so you'd need to use an IF formula in a separate checkbox column on your sheet to first determine if the value in the date column is before the end of May.

     

    Insert a new checkbox column in your sheet and use an IF formula to check the box if the date in your date column is before the last day in May:

     

    =IF(Date1 < DATE(2016, 5, 31), 1)

     

    This formula will check the box when the date in the "Date" column is before May 31st, 2016 (you can hide this column, if you want).

     

    Then, you can use a SUMIF formula to sum the values in the contract column for rows where the status is green and the box is checked (date is before May 31st):

     

    =SUMIFS(Contract:Contract, Checkbox:Checkbox, 1, Status:Status, "Green")

     

    You'll want to modify the "Contract", "Checkbox", and "Status" range values to match your column headers and desired value ranges. Let me know how this works for you.

  • Thanks a lot for clarifications. let me try and revert. 

     

    cheers

    z

     

This discussion has been closed.