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

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
bassam.khalil2009@gmail.com
☑️ 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"

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.

Good Idea.
bassam.khalil2009@gmail.com
☑️ 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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!