Re: Counting occurences of a value tied to unique ID's
- From: Domenic <domenic22@xxxxxxxxxxxx>
- Date: Wed, 25 Jan 2006 17:30:12 -0500
Try...
=COUNT(1/FREQUENCY(IF((A2:A100<>"")*(B2:B100="Yes"),A2:A100),IF((A2:A100<
>"")*(B2:B100="Yes"),A2:A100)))
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
In article <1138223866.164092.253270@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
anjogasa@xxxxxxxxx wrote:
> I've hit a wall on summarizing some GIS data. I've tried searching
> google and this group, and while I've found many posts detailing how to
> count uniquely within a column, the situation I'm facing is a little
> more difficult I believe, or perhaps I don't know the correct
> terminology to describe it / search for. I've simplified my actual
> data/situation for examples sake:
>
> Suppose column A is "ID_Number" and column B is "Comment" (for example
> sake "YES", "NO", or "NA"). I need to sum the number of times a
> specific comment, say "YES", appears in column B, but only once per
> unique ID_Number. Say ID_Number 1452 has 4 comments: "YES". "NO",
> "YES", "NA". It should contribute only one "YES" to my count.
>
> I've reached the limits of my knowledge about how to approach this. So
> far I can make 1 more columns, column C. Column C contains a "1" if
> this is the first time the ID number has shown up (obviously I must
> sort my data by column A, ascending), and a 0 if not. I could run a
> pivot table at this point using the "Comment" column and column C, and
> get a sum of "YES", however this doesn't quite cover the situation.
> There could be an ID_Number that appears, for the first time, with a
> comment of "NO". Any subsequent "YES" in the comment column for this
> ID, will not be recorded.
>
> Simply because of time constraints, I don't want to have to write a
> macro (I've had to learn a bit of VB for Office over the last few days,
> mostly macro'ing Pivot Table creation and manipulation). Any idea if
> this can be done without resorting to a (complex?) macro.
>
> The general flow of the macro would go (I believe)
>
> 1. For each cell in comment column
> 2. If the comment = "YES"
> 3. Check if the associated ID_Num occurs in some list
> 4. If not add it to list
> 5. Finally sum the list
>
> Any thoughts?
.
- References:
- Counting occurences of a value tied to unique ID's
- From: anjogasa
- Counting occurences of a value tied to unique ID's
- Prev by Date: Re: Merging Columns - First Name Initial & Full Last Name
- Next by Date: Re: Merging Columns - First Name Initial & Full Last Name
- Previous by thread: Counting occurences of a value tied to unique ID's
- Next by thread: Need formilas value to dynamicaly update on 2 different worksheets in the same spreadsheet
- Index(es):
Relevant Pages
|
