Dynamic subtotals?

[jackz]

Hi everyone,

this is my first post in the forum.

I have a question for all you experts: I have a list of people attending an event - which is going to be updated in the next days - and my boss wants me to create a summary page with the names of the companies these people work in, and the number of attendees for each of them.

Practical example:

this is the input sheet:

Name - Company
Mr.X - ABC Ltd
Ms.Y - ABC Ltd
Eddie - XYZ Inc.
... - ...


The output should look like

Company - Attendees
ABC Ltd - 2
XYZ Inc. - 1
etc.etc.

I hope it's clear

It'd be pretty easy if I were allowed to use subtotals, but - since the worksheet will be updated - it should be dynamic. Could anyone give lend me a hand of this? It'd be much appreciated.
Thanks!

j

 

[jackz]

sorry for the double posting.

I have tried using a drop-down list and a COUNTIF function, but names on the list have to be manually chosen anyways

 

[mikerickson]

It sounds like a pivot table would work.

It also looks like =COUNTIF(B1:B10,"XYZ corp") is the number of attendees that work for XYZ Corp

 

[jackz]

thanks mike! The pivot table might work indeed... I just have to find a way so that the table's size (its rows) increases automatically as new companies are entered in the input sheet.
Is it possible to define the pivot table data-range in such a way?

 

[jackz]

Solved! Thanks a lot. I used the function OFFSET to define the pivot table data range