Re: Counting Groups

Tech-Archive recommends: Speed Up your PC by fixing your registry

I still can't get a normal pivot table to work because it seems like it
is counting the duplicate locations, for example item A it says 3 in
the pivot table although it is really only in 2 unique locations.

Thanks for the website link. I added the fomula
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) to column D and this
seems to correctly tell if it is unique. By including this column in
the pivot table, I am able to tell who many locations that item is in.
I am not sure I understand what that formula is doing....could you
explain some?

Thanks,
Andrew V. Romero


Jim Thomlinson wrote:
Give this a look...

http://www.contextures.com/xlPivot07.html#Unique
--
HTH...

Jim Thomlinson


"rrstudio2@xxxxxxxxxxx" wrote:

I have a data table like

Item Location Qty
A 1 3
B 2 6
C 3 7
A 4 5
A 1 3
C 5 3

I am trying to count how many locations each item is in so I would want
it to say item A is in 2 locations (locations 1 and 4), item B is in 1
location, item C is in 2 locations, etc. I thought this would be easy
but can't seem to get it to work with pivot tables or formulas. So
whats the best way to do this???

-Andrew V. Romero



.

Relevant Pages

  • Re: Counting Groups
    ... Essentially it is a countif with two criteria. ... the pivot table, I am able to tell who many locations that item is in. ... Jim Thomlinson wrote: ... -Andrew V. Romero ...
    (microsoft.public.excel.programming)
  • RE: pivot table wont calculate
    ... "Jim Thomlinson" wrote: ... In a sperate workbook try recreating the pivot table. ... piv tbl, and Excel exhibited the same behavior (progress bar goes halfway, ... The column area consists of dates, ...
    (microsoft.public.excel.misc)
  • RE: pivot table wont calculate
    ... "gtgtgt" wrote: ... "Jim Thomlinson" wrote: ... taken Pivot tables as high as 3 million records with no problem. ... the Excel status bar displays the progress bar and a message ...
    (microsoft.public.excel.misc)
  • RE: Preserving Highlights in Pivots
    ... I want it to be this long HHHHH, when i update the pivot it expands to this ... "Jim Thomlinson" wrote: ... "Schwimms" wrote: ... the table options for auto format becomes checked again. ...
    (microsoft.public.excel.misc)
  • RE: Automatically Refresh pivot table
    ... "Shell" wrote: ... the code is running, I put in a break point, but the pivot table did not ... I added a line in the data tab with a large numeric value. ... "Jim Thomlinson" wrote: ...
    (microsoft.public.excel.misc)