MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


Posted by Mark W. on December 11, 2001 7:50 AM

Have you considered using PivotTables? (nt)

Posted by Brett on December 13, 2001 2:57 AM

Re: Have you considered using PivotTables? (nt)

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.

Posted by Mark W. on December 13, 2001 12:08 PM

Not if...

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