+ Reply to Thread
Results 1 to 16 of 16

Calculate cumulative sums and obtain highest value based on it

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2016
    Posts
    15

    Post Calculate cumulative sums and obtain highest value based on it

    Hello,

    In my large data table with many columns, I have a column for "Month", "Agent" and "Data".

    I also have two separate cells located outside the table: H1 (which will have values entered like "2018"), and J1 (which will have values entered like "JAN").

    In the table, the month column has values like "2019 JUN" (for all periods from 2012-2020), the "Agent" column has names of people (duplicated for each month), and the "Data" column has a number.

    I want to get the highest cumulative sum of "Data" based on the totals of each individual, and which agent achieved that highest total, IF two separate cells (H1 and J1) match part of the content in the corresponding "Month" column.

    The agent name "TOTAL" should be completely ignored.

    Please see the attachment, as it gives a clearer picture.

    Thank you for any help.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Calculate cumulative sums and obtain highest value based on it

    Wouldn't a pivot table be best for this?

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,232

    Re: Calculate cumulative sums and obtain highest value based on it

    Array formulas

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Calculate cumulative sums and obtain highest value based on it

    MMULT necessary since OP's Month column is YYYY MMM text. If only it were date values (numbers) just formatted like that, then it'd be possible to use a shorter formula

    =MAX(SUMIFS(Table1[Data1],Table1[Agent],Table1[Agent],Table1[Agent],"<>TOTAL",Table1[Month],"<="&--(J1&H1)))

  5. #5
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,232

    Re: Calculate cumulative sums and obtain highest value based on it

    Quote Originally Posted by hrlngrv View Post
    MMULT necessary since OP's Month column is YYYY MMM text.
    Yes and the options #3 ,№4 based on Eng regional settings ( --(J1&H1) ).
    But i see "It is preferred if no additional helper columns are used. The concatenate column already exists in the original table, and can be used if needed." in the file.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,245

    Re: 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.
    Attached Files Attached Files
    Glenn



  7. #7
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2016
    Posts
    15

    Re: Calculate cumulative sums and obtain highest value based on it

    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
    Attached Files Attached Files
    Last edited by AliGW; 11-07-2020 at 09:54 AM. Reason: PLEASE don't quote unnecessarily!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,245

    Re: Calculate cumulative sums and obtain highest value based on it

    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2016
    Posts
    15

    Post Re: Calculate cumulative sums and obtain highest value based on it

    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.
    Attached Files Attached Files
    Last edited by Rehman5000; 11-09-2020 at 04:58 AM. Reason: I confused myself. Let me rephrase everything

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,245

    Re: Calculate cumulative sums and obtain highest value based on it

    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?
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2016
    Posts
    15

    Re: Calculate cumulative sums and obtain highest value based on it

    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?
    Last edited by Rehman5000; 11-09-2020 at 01:42 PM. Reason: typo

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,245

    Re: Calculate cumulative sums and obtain highest value based on it

    Populate with some manually calculated results.

  13. #13
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2016
    Posts
    15

    Re: Calculate cumulative sums and obtain highest value based on it

    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.
    Attached Files Attached Files
    Last edited by Rehman5000; 11-10-2020 at 12:45 AM.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,194

    Re: Calculate cumulative sums and obtain highest value based on it

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  15. #15
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2016
    Posts
    15

    Re: Calculate cumulative sums and obtain highest value based on it

    Thanks Jete, the highlighted cells generated the expected results.

    With regards to the other two tables, we have decided to drop them from our reports, so it is no longer needed to work on them.

    Thank you for all the help, Glenn, Jete, and others.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,194

    Re: Calculate cumulative sums and obtain highest value based on it

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Match criteria (year) and obtain averages and sums
    By student789 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-05-2019, 03:12 AM
  2. [SOLVED] Calculate cumulative sum based on index value
    By jonnyhtfc90 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2019, 09:08 AM
  3. need help with excel formula to calculate based on cumulative
    By zr3cool in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2018, 03:36 PM
  4. Calculate sums based on date range
    By konbanwa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2017, 02:42 PM
  5. Calculate multiple cells sums based upon another cell
    By swiftworks in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 11:44 AM
  6. [SOLVED] Cumulative sums
    By kipronopaul in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2012, 08:21 AM
  7. Replies: 6
    Last Post: 05-23-2008, 01:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1