Sum of Max Value across multiple rows

Techspan CT · 04/08/21

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.

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

Techspan CT · 04/08/21

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 · 04/13/21

@Techspan CT

Good Idea.

Sum of Max Value across multiple rows

Answers

Help Article Resources

Categories