Pivot table problem

Topic

New Reply

Actually, I think the pivot table is fine.. the problem lies with me.

I use a spreadsheet to manage staffing at events. I have various staff at an event and create a row for each person that has the event number and city and so on (see attached). I wanted to use a pivot table to see how many events were performed per city but what I get is the count of staff used at each city.

In the attached worksheet, I have some sample data – the answer is one event per city but as you can see, I don’t get that. I want to continue managing the staff this way (until I find something better) but need to be able to look at the data as I need.

i would appreciate any help.

Reply | Quote

Replies

Pivot tables don’t count unique values, they just count rows.

You can get your count of events by using Advanced Filter twice. To get the attached version I did the following:
– Copy the column headings City and Event # to I1:J1 and to L1:M1.
– Click in the data table, and select Data | Filter | Advanced Filter…
– Select the option to copy records to a different location, specify I1:J1 as destination and tick the check box “Unique records only”.
– Click OK. This produces the table in columns I and J, with unique City/Event # combinations. (I modified the data slightly to better see the effect.)
– Click in the data table again, and select Data | Filter | Advanced Filter…
– Select the option to copy records to a different location, specify only L1 (not L1:M1) as destination, and tick the check box “Unique records only”.
– Click OK. This produces the list of unique cities in column L.
– In M2, enter the formula =COUNTIF($I$2:$I$11,L2) and fill down.

Reply | Quote

Given the data, Han’s solution is quite corrrect.

However, if you were to add a new Col of Data, if practiable, you could use a Pivot Table.

See the attached file. To ensure it worked correctly I added data so that there were mutilple events in the same city.

Hope this helps.

Reply | Quote

Tom, your attachment is missing…

Reply | Quote

having trouble with my Browser and the Lounge.

I Hope it works this time.

Tom Duthie

Reply | Quote

I Hope it works this time.

Yes, the attachment is present.

Reply | Quote

Thank you both for the responses – two good solutions.

Reply | Quote