I have a spreadsheet for a festival. Unique identifier is ticket number. I want to query the column with tent number, to automatically generate two columns. First column has tent number. Second column has total number of occurrences of that tent number.

sample data. Column K and L are auto generated

I want Column K and L to autogenerate tent number and number of occupants based on column J. How can I do this?

2 Answers 2


Select ColumnJ. Data > Pivot table report..., Rows - Add field Tent#, Values - Add field Tent#. Click on down arrow next to "Summarise by: SUM" and select COUNT. Select the PT, Ctrl+c, got to K2(?) in your data sheet and Ctrl+v.

  • 1
    A.Maz.ING. Again. You've just saved hours and hours of work. We're now totally sorted for tents. The process can be handled entirely by me now. You've saved us alot of time, money and effort. Thanks so much
    – Malius
    Commented Nov 20, 2014 at 8:24

I want to find the number of occurrences of, say, "X" within a range of cells, where "X" may occur multiple times within any cell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.