+ Reply to Thread
Results 1 to 5 of 5

Counting a Change in Data in a Subtotal

  1. #1
    stacy
    Guest

    Counting a Change in Data in a Subtotal

    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.


  2. #2

    Re: Counting a Change in Data in a Subtotal

    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


  3. #3
    stacy
    Guest

    Re: Counting a Change in Data in a Subtotal

    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


  4. #4
    Dave Peterson
    Guest

    Re: Counting a Change in Data in a Subtotal

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

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

    Then you could just sum that total.

    stacy wrote:
    >
    > 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.


    --

    Dave Peterson

  5. #5
    stacy
    Guest

    Re: Counting a Change in Data in a Subtotal

    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!!!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1