??? FREQUENCY/COUNT

[Danni2004]

My last post was a reply and didn't move to the top so I am posting it again
in hopes that someone might help me reolve my dilemma.

I needed to find out the total number of stores that were listed in several
thousand rows of data. Many rows had the same store (575 possible stores). I
cannot move the data around by sorting or subtotaling. A few of weeks ago I
found this formula in the MS Discussions:
=COUNT(1/FREQUENCY($A$2:$A$8385,$A$2:$A$8385))

This seemed to work; however, I also wanted to break this total count down
by region (eight possible different regions). For this, I ended up just
assigning the arrays in groups with the exact range for each region since the
data was already sorted by region. This worked okay but I know there must be
a better way to do it.

Now I need to count the total number of employees (in total and broken down
into regions). Each employee has multiple rows so I was trying to use their
SSN. Unfortunately, I get zeroes. The SSN is in the “general†number format,
which is the same as the store and the region columns. Below is a sample of
my data (SSNs are fictitious). Advice?

SSN Store Region
523624500 00977 1
523624500 00977 1
144841174 00323 2
144841174 00323 2
292212044 08015 4
104685201 08025 4
176828434 08006 6
222707744 08006 6
234790315 00698 8
698015143 00698 8

Total Count ??? 6

Region 1 Stores ??? 1
Region 2 Stores ??? 1
Region 3 Stores ??? 0
Region 4 Stores ??? 2
Region 5 Stores ??? 0
Region 6 Stores ??? 1
Region 7 Stores ??? 0
Region 8 Stores ??? 1

Thanks again!
Danni

 

[rdwj]

Danni,

have you ever looked at pivot tables? This problem is a typical problem that
can be easily solved with a pivot. Select the data, start the pivot wizard
and go through that.
put the region in the rows, count of SSN in the data area.
to find no of stores in region, make a second pivot, again region in row,
count of stores in data area.
Note that pivot's do NOT update automatically ! Right click, Update pivot.
R

 

[Danni2004]

Thanks R
Unfortunately, Pivot Tables are not an option either. Any other suggestions?