count values in a column, but. . .

[tree]

First, apologies: My question must be answered in this and countless other
forums, but I can scarcely even begin to 'formulate' my question in search
syntax. So here goes.

I have a table with the columns Name (of people) and Town. (There are other
columns, but these are the two I'm concerned with.) I want to count the
number of people there are for each Town, but count each person only once.

For instance -

NAME TOWN
person1 town1
person1 town1
person2 town1
person3 town2
person3 town2
person4 town3


The results I'm looking for would be -

TOWN COUNT (of individual people)
town1 2
town2 1
town3 1

Thanks.

 

[Roger Govier]

Hi

Assuming your source data is in columns A and B of Sheet1 and your
results are on Sheet2, with Town 1 in A2
Try
=IF($A2="","",COUNTIF(Sheet1!$B:$B,$A2))
Copy down as required.

 

[tree]

Sorry - again - that when I posted my question, the space between the
imaginary columns in my imaginary tables were deleted. Hope it makes sense.

 

[tree]

I stumbled on a solution, but it's sort of clunky.

I created a pivot table with the NAME field in the rows and TOWN in columns,
with TOWN in the data section. The pivot table very nicely counted the
number of times each person was associated with a particular town and very
nicely totaled this up - which is exactly what I didn't want.

But then I inserted a new row of cells at the very bottom of the pivot
table, and used the COUNT function in each cell below each TOWN column. Of
course, the range of each COUNT formula only included cells in the data
portion of the pivot table, immediately above.

This worked, but there must be a simpler, more elegant way. If anyone
knows, I'd sure appreciate your sharing.

Thanks.

 

[Ziggy]

I don't believe that Roger's formula will work, It will result in
3,2,1 not the 2,1,1.

I don't have an elegant single formula solution. My solution is for
2007. For 2003 you would need an array formula. Her goes.

In a new column, add the two columns: = A2&B2 for the entire list.

Copy and PasteSpecial Values into a new column.

2007 allows you to remove Duplicates in the Data tab. Else, sort
alphabetically, and use an IF formula to compare consecutive fields.

=IF(G2=G3,2,1); then eliminate all the 2's

Once you have unique fields, new column =right(H2,6) will give you the
cities.

Then do the =countif(Column,Reference)

Not elegant but the result is the number of unique person/city
combinations

 

[T. Valko]

Try this array formula**.

Data in the range A2:B7. There are no empty cells *within* the person name
range A2:A7.

D24 = list of unique town names: town1, town2, town3

Enter this array** formula in E2 and copy down to E4:

=SUM(IF(FREQUENCY(IF(B$2:B$7=D2,MATCH(A$2:A$7,A$2:A$7,0)),ROW(A$2:A$7)-ROW(A$2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Roger Govier]

Hi

Ziggy is quite right.
My apologies, I did not read the question properly.

The following will produce the answer you want.

On Sheet1 add the following formula in C2 and copy down
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"",
SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)))

Then on Sheet 2 in B2 enter the following
=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!C:C)
and copy down

 

[Ziggy]

Hi

Ziggy is quite right.
My apologies, I did not read the question properly.

The following will produce the answer you want.

On Sheet1 add the following formula in C2 and copy down
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"",
SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)))

Then on Sheet 2 in B2 enter the following
=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!C:C)
and copy down

--
Regards
Roger Govier












- Show quoted text -

Nice solution Biff.

 

[Ziggy]

Hi

Ziggy is quite right.
My apologies, I did not read the question properly.

The following will produce the answer you want.

On Sheet1 add the following formula in C2 and copy down
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"",
SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)))

Then on Sheet 2 in B2 enter the following
=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!C:C)
and copy down

Roger,

I tried responding to your email, thank you very much for that, but it
bounces back to me.

I had posted a question directly to you regarding a SUMIFS question.
Per chance did you get that?

POlease copy me again with an email that I can respond to

Siegfried

 

[Roger Govier]

Hi Siegfried

To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[T. Valko]

Thanks!

--
Biff
Microsoft Excel MVP

Hi

Ziggy is quite right.
My apologies, I did not read the question properly.

The following will produce the answer you want.

On Sheet1 add the following formula in C2 and copy down
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,"",
SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)))

Then on Sheet 2 in B2 enter the following
=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!C:C)
and copy down

--
Regards
Roger Govier












- Show quoted text -

Nice solution Biff.

 

[Ziggy]

btrw Bill,

Thanks again. I was approached by a co-worker today with a need for
unique occurences in two columns. You made me look great. I pulled out
this formula and it was exactly what she needed.

Of course she thinks I'm briiliant.

 

[T. Valko]

Of course she thinks I'm briiliant.

Well, aren't you?

Consider this...

Even if you didn't know how to do it initially, you knew where/how to find a
solution.

I keep a library of formulas. If I need to write a fairly complex formula I
could do it from scratch and it would take me x amount of time to write it
and test it. However, chances are pretty good that I already have a generic
version of this formula in my library. So, all I have to do is look it up!

Some things are kind of complex and it's not easy remembering exactly how it
should be written. The library comes in handy!