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.

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

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

Sounds like a simple COUNTIF() function will do the job:

=COUNTIF(B:B,"PG")

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.

Have a look at the Help for COUNTIF and SUMIF

=COUNTIF(B:B,"PG")

or

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

Regards

Trevor


"ACDenver" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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.