Archive of Mr Excel Message Board
Back to Pivot Tables in Excel archive index
Back to archive home
Counting names
Posted by Brett on December 11, 2001 7:46 AM
Survey data for resturants is entered in rows:
eg:
a,b,c
Restaurant, Surveyor, Rating out of 5
Sunshine Cafe, J.Smith, 4
A list of Restaurants running down G are used to provide validated entries.
The list of surveyors running down J is used to validate entries.
The following formulae are used:
In L, the number of surveys:
=COUNTIF(a$1:a$1000,G3)
average score (ignore zeroes):
=IF(L3<>0,SUMIF(a$1:a$1000,G3,D$3:D$1001)/L3,"")
What I would like is a formula to calculate the number of different surveyors for each restuarant, to check if the same person is going back and givving it a high score.
Have you considered using PivotTables? (nt)
Posted by Mark W. on December 11, 2001 7:50 AM
Re: Have you considered using PivotTables? (nt)
Posted by Brett on December 13, 2001 2:57 AM
Yes but as the data is being continually added to row by row, it would mean continually re-creating the pivot table data each time a new set of data was added.
Not if...
Posted by Mark W. on December 13, 2001 12:08 PM
...you named your data list range (e.g., Database),
used that name at Step 2 of 3 in the PivotTable
wizard, and updated the "Refers to" range for
the defined name when you added new data.
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.