+ Reply to Thread
Results 1 to 8 of 8

Count logic question

  1. #1
    ACDenver
    Guest

    Count logic question

    How does one "count" the frequency of one column that is dependant on
    another. For example column A is a range of values, and column b is a unique
    identifier(criteria).

    A B

    100 PG
    200 SG
    750 PG
    50 SG
    75 TG

    Can one use a function in a single cell to count all the values associated
    with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    rows like this example, and we are trying to avoid entering a lookup or some
    of type function on each adjacent row.

  2. #2
    Registered User
    Join Date
    05-18-2005
    Posts
    60
    In the cell directly below the last entry in row B, try this:

    =COUNTIF(B1:B5, "PG")

    This will return the count for all the PG's in the "B" column.

    HTH,

    PZan

  3. #3
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Quote Originally Posted by ACDenver
    How does one "count" the frequency of one column that is dependant on
    another. For example column A is a range of values, and column b is a unique
    identifier(criteria).

    A B

    100 PG
    200 SG
    750 PG
    50 SG
    75 TG

    Can one use a function in a single cell to count all the values associated
    with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    rows like this example, and we are trying to avoid entering a lookup or some
    of type function on each adjacent row.
    Hi ACDenver

    To count the number of occurences of say PG assuming the data to be in A1:B5 then use the following > =COUNTIF(A1:B5,"PG") this will return the value 2

    To count the values adjacent to the letters use the following >
    =SUMIF(B1:B5,"PG",A1:A5) this will return the value 850 (100+750)

    Hope this helps

    Paul

  4. #4
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329
    Sounds like a simple COUNTIF() function will do the job:

    =COUNTIF(B:B,"PG")


    Quote Originally Posted by ACDenver
    How does one "count" the frequency of one column that is dependant on
    another. For example column A is a range of values, and column b is a unique
    identifier(criteria).

    A B

    100 PG
    200 SG
    750 PG
    50 SG
    75 TG

    Can one use a function in a single cell to count all the values associated
    with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    rows like this example, and we are trying to avoid entering a lookup or some
    of type function on each adjacent row.

  5. #5
    Trevor Shuttleworth
    Guest

    Re: Count logic question

    Have a look at the Help for COUNTIF and SUMIF

    =COUNTIF(B:B,"PG")

    or

    =SUMIF(B:B,"PG",A:A)

    Regards

    Trevor


    "ACDenver" <ACDenver@discussions.microsoft.com> wrote in message
    news:6C4A5E63-AFBE-4730-ACEE-78EE78717139@microsoft.com...
    > How does one "count" the frequency of one column that is dependant on
    > another. For example column A is a range of values, and column b is a
    > unique
    > identifier(criteria).
    >
    > A B
    >
    > 100 PG
    > 200 SG
    > 750 PG
    > 50 SG
    > 75 TG
    >
    > Can one use a function in a single cell to count all the values associated
    > with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    > rows like this example, and we are trying to avoid entering a lookup or
    > some
    > of type function on each adjacent row.




  6. #6
    Anne Troy
    Guest

    Re: Count logic question

    Sounds like you just need a countif, i.e., =COUNTIF(B2:B500,"PG") will tell
    you how many records were PG.
    ************
    Anne Troy
    www.OfficeArticles.com

    "ACDenver" <ACDenver@discussions.microsoft.com> wrote in message
    news:6C4A5E63-AFBE-4730-ACEE-78EE78717139@microsoft.com...
    > How does one "count" the frequency of one column that is dependant on
    > another. For example column A is a range of values, and column b is a
    > unique
    > identifier(criteria).
    >
    > A B
    >
    > 100 PG
    > 200 SG
    > 750 PG
    > 50 SG
    > 75 TG
    >
    > Can one use a function in a single cell to count all the values associated
    > with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    > rows like this example, and we are trying to avoid entering a lookup or
    > some
    > of type function on each adjacent row.




  7. #7
    Earl Kiosterud
    Guest

    Re: Count logic question

    AC,

    You don't say what the values column (A) has to do with anything. Are there
    PG codes with blank or zero that should be excluded from the count?

    If not, and you have a lot of these and don't want to create a mess of
    COUNTIF formulas, you might want to use a pivot table. Use column B for
    both the row and the data areas, and set the function to COUNT.
    --
    Earl Kiosterud
    www.smokeylake.com

    "ACDenver" <ACDenver@discussions.microsoft.com> wrote in message
    news:6C4A5E63-AFBE-4730-ACEE-78EE78717139@microsoft.com...
    > How does one "count" the frequency of one column that is dependant on
    > another. For example column A is a range of values, and column b is a
    > unique
    > identifier(criteria).
    >
    > A B
    >
    > 100 PG
    > 200 SG
    > 750 PG
    > 50 SG
    > 75 TG
    >
    > Can one use a function in a single cell to count all the values associated
    > with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    > rows like this example, and we are trying to avoid entering a lookup or
    > some
    > of type function on each adjacent row.




  8. #8
    ACDenver
    Guest

    RE: Count logic question

    I appreciated the response. And allow me to qualify my original question
    further. The sumif and countif worked if one has only once column with
    values, but can one use a sumif for two more aging buckets? I know I can do
    a pivot table and obtain this, but I am trying to avoid that.

    See my example below - the question is how do I do a sumif function when I
    want to recap hundreds of rows by alpha FERC Codes and associated values
    based on an aging bucket? I included an excerpt of 5 records under 4 column
    headings, and then I show the summary of those five records below by FERC
    Code by aging bucket. The countif function worked when it came to counting
    the FERC codes, but I am stuck on calculating the summary values associated
    to the FERC Code by aging bucket with the sumif function. This summary
    section is giving me heartache. Thanks
    FERC Code FERC Current FERC >30 Days FERC > 60
    TG 10
    PG 20
    PG 100
    SG 300
    TG 25
    Total 5 10 420 25

    Count Current FERC >30 FERC >60
    TG 2 10 0 25
    PG 2 0 120 0
    SG 1 0 300 0
    Summary 5 10 420 25




    "ACDenver" wrote:

    > How does one "count" the frequency of one column that is dependant on
    > another. For example column A is a range of values, and column b is a unique
    > identifier(criteria).
    >
    > A B
    >
    > 100 PG
    > 200 SG
    > 750 PG
    > 50 SG
    > 75 TG
    >
    > Can one use a function in a single cell to count all the values associated
    > with PG? For example PG = 2 , and SG = 2, and TG= 1. We have hundreds of
    > rows like this example, and we are trying to avoid entering a lookup or some
    > of type function on each adjacent row.


+ 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