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
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!