Formula to find the the highest sum of a consecutive list of data

MCODY
MCODY ✭✭
edited 01/09/24 in Formulas and Functions

I need help trying to figure out a formula that will find the max consumption qty of a particular item on a given day. The tricky part is that there are multiple instances per day of consumption for each item.

It would need to to understand that each day is also a unique identifier and sum accordingly per item.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots for context? I'm thinking you'll most likely end up with a MAX/COLLECT but want to be sure.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MCODY
    MCODY ✭✭

    The above picture shows an instance where consumption occurred more than one time of the same SKU on the same day. I need the formula to find the max summed consumption of a SKU each day that automatically updates as we continue to operate.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In that case you do need a MAX/COLLECT. Assuming the formula is going on a different sheet, it would look something like this:

    =MAX(COLLECT({Consumption Column}, {Created Date Column}, DATEONLY(@cell) = DATE(yyyy, mm, dd)))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MCODY
    MCODY ✭✭

    DATEONLY(@cell) = DATE(yyyy, mm, dd) would I need to choose a specific date to calculate from or would it continuously search for the max summed consumption?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to chose a date. You can create a table and have multiple dates listed in a column then use a cell reference if needed.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MCODY
    MCODY ✭✭

    Could I make it a date range?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. The criteria would look more like this (same thing goes for the DATE functions as before).

    =MAX(COLLECT({Consumption Column}, {Created Date Column}, AND(DATEONLY(@cell) >= DATE(yyyy, mm, dd), DATEONLY(@cell) <= DATE(yyyy, mm, dd))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MCODY
    MCODY ✭✭

    I tried utilizing this formula to satisfy the equation. However, I am getting an invalid operation. Is the issue DATEONLY(@cell) being replaced by a column reference to another sheet? If so how do I rectify this?


    =MAX(COLLECT({Inventory Manager QTY}, {Inventory Manager SKU #}, SKU@row, AND(DATEONLY({Inventory Manager Created Date}) >= DATE(2023, 1, 1), DATEONLY({Inventory Manager Created Date}) <= DATE(2023, 12, 31))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. You need to leave it as @cell.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MCODY
    MCODY ✭✭

    What does the @cell refer to? Should it be referencing something in my Inventory Master Sheet (where the calc is being performed) or the Inventory Manager (where the data is being collected)?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No. You just leave "@cell" exactly how and where it is.


    It basically tells the formula to evaluate the previously established range on a cell by cell basis.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MCODY
    MCODY ✭✭

    Happy New Year!

    I'm circling back to this and need to build this formula in another sheet referencing our Consumption tracking sheet.

    Below are the criteria


    If - it equals the SKU description in the SKU column in Sheet A in the Consumption Tracker

    If - the action type is " Consumption" in the consumption Tracker

    If - it is within a Quarter date Range e.g. Q4 (10/1/2023 - 12/32/2023)

    Input the previous formula and made references, but it now says it is an invalid data type.

    Any help closing this out would be greatly appreciated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!