I am trying to figure out how to get subtotal counts of stores by region with
the following data:
-10000+ rows of data
-each row has a cell indicating what region it is in (any one of 8 regions)
and a cell indicating what the store number is (any one of 575 stores) so
there are numerous lines for each region and each store.
-I added an add'l column that displays the store number in a cell on each
row only if the line meets a certain criteria (using IF statement) so some of
these cells in the new column have a value and some don't.
Table Example:
A B C D
EC Reg Str StrIF
1 21 3 153
2 30 6 327 327
3 34 8 519 519
4 18 6 361
5 31 5 482 482
6 30 8 519 519

I was able to get a total count of stores for column D (each store listed
counted only once) with the count & frequency functions but would like to
break this same count down by region at the bottom of the data (Ex: Total Ct.
Reg 8=2, Total Ct. Reg 3=0, etc.).
Anyone have a formula that might work?

Thank you!

Have you considered using a Pivot Table?

Select your data range

Then
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Reg field here
DATA: Drag the StrIF field here
If it doesn't list as Count of StrIF...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done.

That will list each Reg and the count of StrIF stores.

The end result will look like this:
Count of StrIF
Reg Total
3
5 1
6 1
8 2
Grand Total 4

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Danni2004" wrote:

I am trying to figure out how to get subtotal counts of stores by region with
the following data:
-10000+ rows of data
-each row has a cell indicating what region it is in (any one of 8 regions)
and a cell indicating what the store number is (any one of 575 stores) so
there are numerous lines for each region and each store.
-I added an add'l column that displays the store number in a cell on each
row only if the line meets a certain criteria (using IF statement) so some of
these cells in the new column have a value and some don't.
Table Example:
A B C D
EC Reg Str StrIF
1 21 3 153
2 30 6 327 327
3 34 8 519 519
4 18 6 361
5 31 5 482 482
6 30 8 519 519

I was able to get a total count of stores for column D (each store listed
counted only once) with the count & frequency functions but would like to
break this same count down by region at the bottom of the data (Ex: Total Ct.
Reg 8=2, Total Ct. Reg 3=0, etc.).
Anyone have a formula that might work?

Thank you!