Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Create Unique List & Summarize All Associated Values

1. I agreed to these rules
Join Date
30th January 2009
Posts
4

## 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,

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

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

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

3. I agreed to these rules
Join Date
30th January 2009
Posts
4

## 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.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

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

You are welcome !!

Keep EXCELling !!

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

You might consider a PivotTable.

##### Users Browsing this Thread

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

#### Posting Permissions

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