How to find date having the highest sum when multiple entries per day in a list

I have a running list which collects values submitted by a form. There are multiple users that can submit the form multiple times per day to record a value . I am trying to find a formula which finds the highest summed value for any one day. In this case, the higher the value submitted the worse it is, so I am trying to find the "worst day" out of all entries. Any help provided is truly appreciated.

Best Answer

  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓
    =SUMIF({# redacted alerts triaged},{date triaged},Date@row)
    

    Try to use this on another sheet -

    Col 1 = Running Date, from 1/1/24 on wards

    Col 2 = The formula

    Anything in curly brackets are the references to the Source column. This should you get the sum for each days.

    ...

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 01/30/24
    =SUMIF(Value:Value,Date:Date,Date@row)
    

    You might need a separate sheet for this one. Or use the sheet summary. If that is the case, you will have to change the last arguement Date@row

    ...

  • Thank you for responding HeyJay, I tried to use that formula + sheet summary, however I could not get it to work. I am including a screen shot of my sheet (column with submitter's name isn't shown). I am trying to find the day that had the highest sum of all the alerts triaged on that day. If I do that, I can index match the sum value from there (I think). Note: The grey columns are helpers for a reference metrics sheet that I use to collect info. I could add rows for all 365 days to the metric sheet, sum the values for each and then it would be easy to find the max value. I am just trying to avoid that or using pivot by having a formula.


  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓
    =SUMIF({# redacted alerts triaged},{date triaged},Date@row)
    

    Try to use this on another sheet -

    Col 1 = Running Date, from 1/1/24 on wards

    Col 2 = The formula

    Anything in curly brackets are the references to the Source column. This should you get the sum for each days.

    ...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!