Sum of Max Value across multiple rows

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 Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 04/08/21

    Hi @Techspan CT

    Hope you are fine, you can use the following formula

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

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


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Techspan CT
    Techspan CT ✭✭✭

    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 Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Techspan CT 

    Good Idea.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!