Calculate cumulative sums and obtain highest value based on it

Try this for the Agent Name:
=IF($H5="","",INDEX($C$2:$C$40,MATCH($H5,INDEX(SUMIFS($D$2:$D$40,$B$2:$B$40,$G$1&" "&$H$1,$C$2:$C$40,$C$2:$C$40)*(COUNTIF($G$4:$G4,$C$2:$C$40)=0),0),0)))

and this for the amount:
=IFERROR(1/(1/LARGE(INDEX(SUMIFS($D$2:$D$40,$B$2:$B$40,$G$1&" "&$H$1,$C$2:$C$40,"<>Total",$C$2:$C$40,$C$2:$C$40)*(COUNTIF($G$4:$G4,$C$2:$C$40)=0),0),1)),"")

You need to open the sheet, to see where the formulae are placed. If you want other names, in rank order, returned, just drag both formulae down (as you previously asked for a list of all names in alphabetical order... I replied in that thread, but you seem to have ignored my reply).

There are similar formulae in this sheet witht the year and both year/month criteria omitted.

Hi Glenn,

Thank you (and everyone else) so much for looking into this.

This seems like it will work, but I am having trouble moving the formulae to the format/structure I need.

I should've added this to my original post, but could you please see the "Output" sheet in the updated example Excel attached? That is the exact table I am trying to populate. I have added footnotes on what the values should be.

Would you be able to copy your formulae to that please?

Warmest regards,
Rehman

Hi. Something went seriously weird when testing this. I have fixed it, by tweaking the formula, but I am not 100% certain that I understand why!!

I shall start a thread myself and see if someone else can figure it out!

On your real data, let me know if you get a maximum figure, but no corresponding name is returned.

Hi Glenn,

Thank you, and also to the reply to the private message.

Part of the function seems to work, but I may have confused you with regards to the others. Please see the neater attachment.

I have anonymised the entire 2012-2020 data, so it can be easily tested publicly (and hopefully also help others with a similar need).

In the attached excel, there are four tables in the "Output" sheet, these four tables use the same data in the Data Table located in the "Data" sheet.

The output sheet has a cell (A2), in which the required year is selected/entered (i.e. 2019).

The data table includes 3 main columns:

• Month: Which has a text-based string identifying the year and month in the format "2019 NOV" (it is not originally meant to function as a date, but as an identifier)
• Agent: Which has the name of the agents
• Total: Which has the volumes processed by the agent for the given month/year
• The "Helper1" column (which concatenates the first two columns mentioned) exists in reality for other functions, and can be used if needed

I need to populate the four tables as follows. All tables has columns for each month. The last column (Column T), should show the overall highest. Reading the below descriptions may be confusing, so you can ignore below if you like, and see the attachment for a better understanding.

Table 1 - Highest volumes processed in the selected year

• The "Data1" row should show the highest volumes processed by any single agent, in the respective month, of the selected year
  The corresponding T10 cell should show the highest volumes processed by any single agent for the entire selected year
• The "Agent" row should show the agent that achieved the above.

Table 2 - Highest volumes processed in any single year

• The "Data1" row should show the highest volumes processed by any single agent, in the respective month, for any single year
  The corresponding T10 cell should show the highest volumes processed by any single agent for any single selected year
• The "Agent" row should show the agent that achieved the above.
• The "Year" row should show the year the above was achieved.

Table 3 - Cumulative highest volumes processed in the selected year

• The "Data1" row should show the highest cumulative volumes processed by any single agent up to current month (column), of the selected year
  e.g. Apr should show the highest Data1 value, after the corresponding Jan+Feb+Mar+Apr is added for the selected year
• The "Agent" row should show the agent that achieved the above.

Table 4 - Cumulative highest volumes processed in all years

• The "Data1" row should show the highest cumulative volumes processed by any single agent up to current month (column), of for all years
  e.g. Apr should show the highest Data1 value, after the corresponding Jan+Feb+Mar+Apr is added from all years
• The "Agent" row should show the agent that achieved the above.

Please let me know if anything is confusing.

1 and 2 are clear, easy and done. However, I am not sure what you want for 3 and 4. Check the first row of 3. I have made a guess!! What DO you expect to see here?

Thanks Glenn. But I'm afraid 1 and 2 are incorrect (the highlighted cells). It cannot be a MAX value of each of the month-outputs. For example, a particular agent might have the top highest volume in a particular month only, but another agent could have sustained the 2nd highest over many months, and thus covering more volumes when the whole year is accounted for.

Based on the above principle, the 3rd is incorrect as it cannot be based on the highest, but rather the cumulative highest.

The third table should essentially show this:

• Jan: Simply the highest volume processed in Jan
• Feb: The highest volume processed, AFTER the Jan and Feb volumes are combined
• Mar: The highest volume processed, AFTER the Jan, Feb, and Mar volumes are combined
• ...
• Nov: The highest volume processed, AFTER the Jan,Feb,...,Nov volumes are combined.

The fourth table is similar, but taking in to account the same month of every year:

• Jan: The highest volume processed, AFTER summing up all Januaries (of all years) in the database for each agent
• Feb: The highest volume processed, AFTER summing up all volumes of Jans and Febs for each agent separately
• Mar: The highest volume processed, AFTER summing up all volumes of Jans, Feb for each agent separately
• ...
• Nov: The highest volume processed, AFTER summing up all volumes of Jan's, Feb's,...Nov's, for each agent separately

Makes sense?

Populate with some manually calculated results.

Manual calculation is very tedious, hence the reason I am trying to find a shortcut with formulas

To make explaining simpler, I have added "YearAgent" and "MonthAgent" helper columns. These columns ideally shouldn't exist in the actual sheet, so they should either be incorporated in to the final formula, or should be derived from Helper1 instead.

There is only one "Total" column, so all value reference (i.e. SUMIF), relate to that.

Basically, these are the manual steps to derive the correct figures:

T10 of table 1:

1. Individually SUMIF the "Total" column based on each unique YearAgent, if YearAgent has the value/starts with A2 (of the Output sheet). "Total" should always be ignored.
2. T10 should show the highest value of these individual sums
3. T11 should show the Agent's name that achieved T10

T20 of table 2:

1. Individually SUMIF the "Total" column based on each unique YearAgent. "Total" should always be ignored.
2. T20 should show the highest value of these individual sums
3. T21 should show the Agent's name that achieved T20 (Agent column)
4. T22 should show the Year that achieved T20 (first 4 chars of Month column)

Table 3:

1. Note: I believe the formulas for each subsequent month will mostly be different, hence the confusion earlier.
2. Jan: Simply the highest corresponding "Total" value, when YearAgent starts with A2
3. Feb: The highest value after SUMIF-ing by all the unique YearAgent, when YearAgent starts with A2, and Month has the word JAN or FEB
4. Mar: The highest value after SUMIF-ing by all the unique YearAgent, when YearAgent starts with A2, and Month has the word JAN or FEB or MAR
5. ...
6. Dec: The highest value after SUMIF-ing all the unique YearAgent, when YearAgent starts with A2, and Month has the word JAN or FEB or ... or DEC

Table 4:

1. Same as Table 3, but without the A2 match, so it gives an all-time highest month-over-month cumulative output.

I understand that the formulas/array-formulas of Table 3 and 4 can be quite complex. Hence if at least Table 1 and 2 can be sorted, I might be able to abandon 3 and 4. But it would be nice if all 4 can be done.

This proposal employs pivot tables to do the calculations used to fill the highlighted cells for Tables 1 and 2.
The pivot table for T10:T11 places Agent and Month in the Rows area and Total in the Values area. The Months field is then filtered to display only months containing 2020.
The maximum sales for 2020 is then displayed in cell I2 using: =MAX(K5:K128)
The name of the agent is displayed in cell J2 using: =INDEX(I5:I128,MATCH(I2,K5:K128,0))
On the Output sheet cell T10 is a direct assignment (i.e. =DATA!I2) while T11 is populated using: =INDEX(I5:I128,MATCH(I2,K5:K128,0))
The pivot table for T20:T22 places YearAgent in the Rows area and Total in the Values area. The YearAgent field is filtered to exclude cells ending in "Total".
The maximum is displayed in M2 using: =MAX(N5:N122)
The YearAgent is displayed in N2 using: =INDEX(M5:M122,MATCH(M2,N5:N122,0))
Cell T20 is populated using direct assignment.
Cell T21 is populated using: =RIGHT(Data!N2,LEN(Data!N2)-4)
Cell T22 is populated using: =LEFT(Data!N2,4)
I feel that Glenn's statement about "...some manually calculated results" is being misunderstood. I believe that he is only asking to see a few results in tables 3 and 4 so that we can know what formulas/code should yield.
I also feel that the process would be easier if another column were added to Table1 such that the values in the month column are converted to dates.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.