Announcement

Collapse
No announcement yet.

Create Unique List & Summarize All Associated Values

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create Unique List & Summarize All Associated Values

    Hi,

    I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.

    My table looks like:



    In reality this list could be thousands of rows long.

    “Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).

    What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:



    Note that
    1) only venues that have run at least 2 meetings are shown in the output
    2) even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.


    In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.

    Have been battling with this with pivots, database functions, array formulae etc. for a long time. This forum looks to be full of experts – if anyone can help I would be most grateful!

    Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.

    Thanks,

    Adam
    Attached Files

  • #2
    Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

    Hi Adam,

    Welcome to OzGrid!!

    Try,

    In G5,

    =COUNTIF(B5:B16,G6)

    In K7 and copied down,

    =IF(ROWS($K$7:K7)<=$G$5,INDEX($A$1:$A$16,SMALL(IF($B$5:$B$16=$G$6,ROW($B$5:$B$16)),ROWS($K$7:K7))),"")

    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    In L7 and copied down and across to M10

    =SUMPRODUCT(--($C$5:$C$16<=$G$6),--($A$5:$A$16=$K7),D$5:D$16)

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

      Thanks so much Krishnakumar for your welcoming message and super quick answer. Your time is much appreciated.

      It seems to work perfectly. When I have time I will even try and understand exactly what it is doing!

      This forum might be the best site ever :-) Seems to have some useful tools around the site too.

      Thanks again.

      Adam

      Comment


      • #4
        Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

        Hi Adam,

        You are welcome !!

        Keep EXCELling !!
        Kris

        ExcelFox

        Comment


        • #5
          Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

          You might consider a PivotTable.

          Comment

          Working...
          X