Counting a Change in Data in a Subtotal

[stacy]

Hello...

I have an issue with being unable to figure out the best way to count a
change in data when using the Subtotal function. I have listed my data
range below:

Profile ID St P/T Name
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106200 AK 00 SHELDON JACKSON COLLEGE
00106200 AK 00 SHELDON JACKSON COLLEGE
00106200 AK 00 SHELDON JACKSON COLLEGE
00106200 AK 00 SHELDON JACKSON COLLEGE
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST

If you notice, I technically have 3 schools, all located in Alaska.
What I have done, is used the Subtotal function so that for each change
in the Profile ID (first column), Max the State column. The problem
with this, is it return AK just fine, but this is somewhat misleading.
For my report, I actually need to "count" 3 AK's, not just the One it
returned using Subtotal.

The goal is to count how many schools are in Alaska, which should
return 3. So, there may ba a much easier way to accomplish this, and I
am open to anything. The entire list is quite large, so any help is,
as always, greatly appreciated.

** There is a reason that each school is appearing 4 times like above,
it has to do with the SQL report off the AS400, and the way the data is
output. Unfortunately, I cannot eliminate this prior to the data
output file.

 

[johnhildreth]

Is the data ALWAYS sorted as shown? That is will all instances of
Alaska Pacific University always be together? If so, then you only
need to count the number of times the value in the 4th column changes,
while state remains AK. You can loop through the rows comparing the
value in col 4 to the value in the previous row. Increment a counter
when they are not equal.

HTH,
John

 

[stacy]

Unfortunately, no. This example does not accurately reflect the
majority of data. What I mean by this, is that many of the "groups" of
schools, like Alaska Pacific, will not always be in groups of 4. So,
in the data set above, it could actually look like this:

Profile ID St P/T Name
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106100 AK 00 ALASKA PACIFIC UNIVERSITY
00106200 AK 00 SHELDON JACKSON COLLEGE
00106200 AK 00 SHELDON JACKSON COLLEGE
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST
00106500 AK 00 UNIVERSITY OF ALASKA - SOUTHEAST

 

[Dave Peterson]

How about adding a new column that counts the school the first time it's used:

=IF(COUNTIF($D$22,D2)=1,1,"")
(and drag down)

Then you could just sum that total.

 

[stacy]

Yup... That will work just fine. I incorporated that with the subtotal
for every change in state, and I can get the number of schools in each
state... Perfect. Thanks Dave!!!