Counting unique entries

[HighlanderFil]

Hello,

I've got this problem: huge amount of data, of which three columns ar
important to me: "Year", "Name of Buyer" and "State". I need t
calculate how many times a unique Name of Buyer X in Year Y came fro
State Z. I was using SUMPRODUCT when I had realized that, for example
for year 1996 I had 4 entries with a blank state (which I need to coun
as Unknown) but only 2 unique buyer names. Help a guy out, please!
would simply enter more qualifiers, such as Company A and Company B
but there are plans on expanding the database, so I'd have to re-do i
all then, but if I could find a way to use the unique qualifier, al
I'd have to do is expand the range.

Oh, and I cannot use pivot tables, it has to be formulas only.

Thanks

 

[RagDyer]

Your semantics are very confusing.

Don't really understand this:
<<"calculate how many times a unique Name of Buyer X">>

Are you counting uniques, or simply how many times a particular name matches
the criteria.

Let's start with this scenario, and you can post back with what you don't
like about it.

A1 = year to find (*not* a date)
B1 = name to find
C1 = state to find

A2:C2 - labels, Year, - Name, - State
A3:C100 = data (again, year *not* a date)

Try this formula:

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1)*(C3:C100=C1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

message Hello,

I've got this problem: huge amount of data, of which three columns are
important to me: "Year", "Name of Buyer" and "State". I need to
calculate how many times a unique Name of Buyer X in Year Y came from
State Z. I was using SUMPRODUCT when I had realized that, for example,
for year 1996 I had 4 entries with a blank state (which I need to count
as Unknown) but only 2 unique buyer names. Help a guy out, please! I
would simply enter more qualifiers, such as Company A and Company B,
but there are plans on expanding the database, so I'd have to re-do it
all then, but if I could find a way to use the unique qualifier, all
I'd have to do is expand the range.

Oh, and I cannot use pivot tables, it has to be formulas only.

Thanks!

 

[HighlanderFil]

This is what I meant, sorry it does read awkwardly.

I have 5 entries for the year 1966 that come from the state of UT. O
those 5 entries only 3 are unique names: Business A (repeated twice)
Business B (repeated twice) and Business C. When I count them, I woul
like to get "3" back as my answer. Right now I get 5, because th
formula that I use only takes into consideration that they are in 196
and from UT - and counts the two entries of Bus. A and Bus. B as uniqu
entries. I need to filter them out

 

[Harlan Grove]

...
...

I have 5 entries for the year 1966 that come from the state of UT. Of
those 5 entries only 3 are unique names: Business A (repeated twice),
Business B (repeated twice) and Business C. When I count them, I would
like to get "3" back as my answer. Right now I get 5, because the
formula that I use only takes into consideration that they are in 1966
and from UT - and counts the two entries of Bus. A and Bus. B as unique
entries. I need to filter them out.

If your data were in a range named TBL with year in the 1st col, state in the
2nd col and names in the 3rd col, you could try the array formula

=COUNT(1/FREQUENCY(IF((INDEX(TBL,0,1)=1966)*(INDEX(TBL,0,2)="UT"),
MATCH(INDEX(TBL,0,3),INDEX(TBL,0,3),0)+CELL("Row",TBL)-1),ROW(TBL)))

 

[HighlanderFil]

Didn't work...gave me a zero valu

 

[RagDyer]

As "gruff" as your disposition may be Harlan, I must say that you *do* come
up with these really *fine* formulas.

To HighlanderFil:
The formula *does* work, exactly as you described.

Check your data, or word wrap, and / or CSE (<Ctrl> <Shift> <Enter>!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


...
...

I have 5 entries for the year 1966 that come from the state of UT. Of
those 5 entries only 3 are unique names: Business A (repeated twice),
Business B (repeated twice) and Business C. When I count them, I would
like to get "3" back as my answer. Right now I get 5, because the
formula that I use only takes into consideration that they are in 1966
and from UT - and counts the two entries of Bus. A and Bus. B as unique
entries. I need to filter them out.

If your data were in a range named TBL with year in the 1st col, state in
the
2nd col and names in the 3rd col, you could try the array formula

=COUNT(1/FREQUENCY(IF((INDEX(TBL,0,1)=1966)*(INDEX(TBL,0,2)="UT"),
MATCH(INDEX(TBL,0,3),INDEX(TBL,0,3),0)+CELL("Row",TBL)-1),ROW(TBL)))

 

[Harlan Grove]

Didn't work...gave me a zero value

Did you enter it as an array formula, holding down [Ctrl] and [Shift] keys
before pressing [Enter]? If so, there could be stray space characters in your
table. Wrap the INDEX calls inside TRIM.

 

[Harlan Grove]

As "gruff" as your disposition may be Harlan, . . .

...

I may not have been warm & fuzzy, but I don't think I put on any attitude in my
previous responses in this thread.

 

[HighlanderFil]

D-oh...of course. It worked. Pardon a newbie thanks a lot!!