Counting occurences and displaying in a different sheet
Say your list is on Sheet1, from A1 to A100.
In the "Results" sheet, enter this, in say A1:
=Sheet1!A1
AND, in A2 of the "Results" sheet enter this *array* formula:
=IF(ISERR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$10 0&""),0)),"",INDEX(IF(ISBLANK(Sheet1!$A$1:$A$100), "",Sheet1!$A$1:$A$100),MATCH(0,COUNTIF(A$1:A1,Shee t1!$A$1:$A$100&""),0)))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.
*AFTER* the CSE, copy down as far as necessary to display all the *unique*
team names from Sheet1.
NOW, enter Roger's formula in B1 of the "Results" sheet:
=COUNTIF(Sheet1!A:A,A1)
And copy down to count the number of times each team appeared in Sheet1.
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===
"JDB" wrote in message
...
That returns the count of the number of times that team appears - I need it
to show the team name.
"Roger Govier" wrote:
Hi
If the team names are in column A
=COUNTIF(A:A,"Team Name")
Replace Team name with the actual team required, or a cell reference
holding the team name
--
Regards
Roger Govier
"JDB" wrote in message
...
Hi,
I have a sheet of data with team names in one column and the data
relating
to these teams in other columns. Each row is a scored telephone call
taken by
individual team members. What I need to do is count the number of time
each
team name appears on the list and display this on another 'Results'
worksheet. I have a pivot table that can give me that, but I need to
display
the data on the 'Results' sheet which contains other data too.
Any ideas?
|