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

Options
✭✭
edited 01/09/24

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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)))

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

Could I make it a date range?

• ✭✭✭✭✭✭
Options

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))))

• ✭✭
Options

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))))

• ✭✭✭✭✭✭
Options

Yes. You need to leave it as @cell.

• ✭✭
Options

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)?

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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!