Re: Count and display number of items

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Peo Sjoblom (terre08_at_mvps.org)
Date: 06/26/04 

Date: Sat, 26 Jun 2004 11:29:01 -0400

You could add the names to you pivot source and use them instead of the
numbers,
assume the Numbers including header is in A2:A500, then create a new column
B,
name it Names and use something like

=INDEX(Sheet2!$A$2:$A$150,MATCH(A2,Sheet2!$B$2:$B$150,0))

where Sheet2!A2:A150 is the list with names and Sheet2!B2:B150 the numbers
on that second work***

copy down along the numbers, right click the pivot and select pivot table
wizard,
click Back and change the source range to include the newly created name
column,
click next and select layout and drag replace the numbers with the names to
get a count of names instead 

-- 
Regards,
Peo Sjoblom
(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
"Andy" <wiltshireguyuk@yahoo.co.uk> wrote in message
news:a9320de.0406260709.38e77671@posting.google.com...
> Earl,
>
> Thank you! I'm kind of new to Excel formulas but since you pointed me
> in the right direction I got the chart I wanted after a few attempts.
>
> One more thing - I have a second work***, names in one column and
> there phone number in the next. Is there any way I can link the names
> to the numbers so the names are shown in the pivot table rather than
> the numbers?
>
> Thanks in advance.
>
> Andy.
>
>
> "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:<ev5nSfuWEHA.3472@TK2MSFTNGP09.phx.gbl>...
> > Andy,
> >
> > A pivot table can do just that.  Set the data function to COUNT, if not
> > already.
> >
> > To identify the duplicates, you can sort your table on the telephone
number
> > column, and put in a helper column:
> >
> > =if(A3=A2, "Dup","")
> >
> > Copy down.
> >
> > -- 
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "Andy" <wiltshireguyuk@yahoo.co.uk> wrote in message
> > news:a9320de.0406251053.1a176004@posting.google.com...
> > > Hi there,
> > >
> > > I have one column of telephone numbers, please can someone help me
> > > with a formula to create another column showing how many occurrences
> > > there are of each number. Thanks!
> > >
> > > Andy.
Quantcast