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.

Have a look at the Help for COUNTIF and SUMIF

=COUNTIF(B:B,"PG")

or

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

Regards

Trevor


"ACDenver" wrote in message
...
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" wrote in message
...
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


--
PokerZan
------------------------------------------------------------------------
PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480
View this thread: http://www.excelforum.com/showthread...hreadid=396293

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.

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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=396293

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

=COUNTIF(B:B,"PG")


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.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=396293

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" wrote in message
...
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.