Sum of Max Value across multiple rows

04/08/21
Answered - Pending Review

Hello,

I need some help working out a formula that will let me sum the max value across 2 conditions...

I have a data sheet that is getting rows copied to is when values are changed on multiple project sheets and i need to have a formula on my report sheet that returns the sum of the highest value in a day for each project.

The highlighted numbers are what i need to add together. I can work out how to use the MAX(COLLECT()) formula and return the sum of the max values with todays date but need to somehow have another condition that returns the sum of the MAX for each Project number as well as the current date.

Is this even possible?

My report sheet just has a column for todays date and the total throughput column where the formula is. (See below - At the moment it only works if the value changes only once a day. If we complete 2 tasks in the day the automation copies 2 rows into the sheet above meaning my sum formula isn't accurate.


Answers

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 04/08/21

    Hi @Techspan CT

    Hope you are fine, you can use the following formula

    =IF(ISBLANK([email protected]), "", SUM(MAX(COLLECT([Daily Throughput]:[Daily Throughput], Project:Project, 1100, [Modified Date]:[Modified Date], [email protected]))) + SUM(MAX(COLLECT([Daily Throughput]:[Daily Throughput], Project:Project, 1263, [Modified Date]:[Modified Date], [email protected]))))

    the following screenshot shows the result, you can test it using the following Published Sheet


    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Thanks @Bassam.M Khalil ,

    I neglected to mention that the project numbers are variable... I have noticed you built them into the formula. We can have up to 70 different numbers that it would need to check against.

    So the formula needs to find the MAX in both day and then within each project number. This is why i don't know if it's possible?

    I may have a solution where i can calculate the difference between each number to get the daily throughput at the project level so this sheet simply becomes a sum of all within the date.

  • Bassam.M KhalilBassam.M Khalil ✭✭✭✭✭

    @Techspan CT 

    Good Idea.

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

Sign In or Register to comment.