# Count logic question

1. ## 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. 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. 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. 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.

5. ## 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. ## 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. ## 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. ## 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.

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

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