Count logic question

[Guest]

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.

 

[Trevor Shuttleworth]

Have a look at the Help for COUNTIF and SUMIF

=COUNTIF(B:B,"PG")

or

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

Regards

Trevor

 

[Anne Troy]

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

 

[PokerZan]

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

 

[Paul Sheppard]

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

 

[Morrigan]

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

=COUNTIF(B:B,"PG")

 

[Earl Kiosterud]

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.

 

[Guest]

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