Home |
Search |
Today's Posts |
#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
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count If question | Excel Discussion (Misc queries) | |||
Logic question | Excel Discussion (Misc queries) | |||
Same Question with one more addition of a dash to count | Excel Discussion (Misc queries) | |||
To Bob ( count question ) | Excel Discussion (Misc queries) | |||
Count function question | Excel Worksheet Functions |