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

Zafar Yuldashev

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. 

Kennedy Stomps

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.

Zafar Yuldashev

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

 

cheers

z