Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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. -- Regards Roger Govier tree wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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. "tree" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
Try this array formula**.
Data in the range A2:B7. There are no empty cells *within* the person name range A2:A7. D2:D4 = 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. -- Biff Microsoft Excel MVP "tree" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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 Ziggy wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
On Mar 25, 10:50*am, Roger Govier
wrote: 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 Ziggy wrote: 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- Hide quoted text - - Show quoted text - Nice solution Biff. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
On Mar 25, 10:50*am, Roger Govier
wrote: 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 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
Hi Siegfried
To mail direct roger at technology4u dot co dot uk Change the at and dots to make valid email address -- Regards Roger Govier Ziggy wrote: On Mar 25, 10:50 am, Roger Govier wrote: 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 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
Thanks!
-- Biff Microsoft Excel MVP "Ziggy" wrote in message ... On Mar 25, 10:50 am, Roger Govier wrote: 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 Ziggy wrote: 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- Hide quoted text - - Show quoted text - Nice solution Biff. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count values in a column, but. . .
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! -- Biff Microsoft Excel MVP "Ziggy" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
Count entries in one column based on values in another column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Can I count values in column 1 if criteria in column 2 are met | Excel Worksheet Functions |