|
|
Don't Count Duplicates
Here's a subset of 3 colunms of data:
Zip Code Name County
42125 John Franklin
42156 John Fairfield
43123 Bill Fairfield
45612 Jane Fairfield
45126 Jane Franklin
49856 Jane Fairfield
45895 Chris Fairfiled
46289 Chris Fairfield
42194 Chris Fairfield
42312 Chris Franklin
I am looking for a formual to count the number of people assigned to each
Territory.
The answer based on thes sample data above would be:
Fairfield - 4
Franklin - 3
Thank you
|
|
0
|
|
|
|
Reply
|
 Utf
|
4/24/2010 4:01:01 PM |
|
Criteria start in E2 and down
In F2: =SUM(N(FREQUENCY(IF(County=E2,MATCH(Name,Name,)),MATCH(Name,Name,))>0))
ctrl+shift+enter, not just enter
copy down as far as needed
"Johnny" wrote:
> Here's a subset of 3 colunms of data:
>
> Zip Code Name County
> 42125 John Franklin
> 42156 John Fairfield
> 43123 Bill Fairfield
> 45612 Jane Fairfield
> 45126 Jane Franklin
> 49856 Jane Fairfield
> 45895 Chris Fairfiled
> 46289 Chris Fairfield
> 42194 Chris Fairfield
> 42312 Chris Franklin
>
> I am looking for a formual to count the number of people assigned to each
> Territory.
>
> The answer based on thes sample data above would be:
>
> Fairfield - 4
> Franklin - 3
>
> Thank you
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/24/2010 4:52:01 PM
|
|
Try this array formula**.
B2:B11 = Name
C2:C11 = County
E2 = Fairfield
E3 = Franklin
Array entered** in F2 and copied down to F3:
=SUM(IF(FREQUENCY(IF(C$2:C$11=E2,MATCH(B$2:B$11,B$2:B$11,0)),ROW(B$2:B$11)-MIN(ROW(B$2:B$11))+1),1))
Assumes no empty cells in the Name range B2:B11.
** 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
"Johnny" <Johnny@discussions.microsoft.com> wrote in message
news:9A1A48A4-9E53-41A5-996C-A47273288F22@microsoft.com...
> Here's a subset of 3 colunms of data:
>
> Zip Code Name County
> 42125 John Franklin
> 42156 John Fairfield
> 43123 Bill Fairfield
> 45612 Jane Fairfield
> 45126 Jane Franklin
> 49856 Jane Fairfield
> 45895 Chris Fairfiled
> 46289 Chris Fairfield
> 42194 Chris Fairfield
> 42312 Chris Franklin
>
> I am looking for a formual to count the number of people assigned to each
> Territory.
>
> The answer based on thes sample data above would be:
>
> Fairfield - 4
> Franklin - 3
>
> Thank you
|
|
|
0
|
|
|
|
Reply
|
T
|
4/24/2010 5:39:51 PM
|
|
|
2 Replies
578 Views
(page loaded in 0.096 seconds)
Similiar Articles: Don't Count Duplicates - microsoft.public.excel.misc ...Here's a subset of 3 colunms of data: Zip Code Name County 42125 John Franklin 42156 John Fairfield 431... Count but ignore duplicate data - microsoft.public.excel.misc ...Hi Expert, I need your help on how to count data but to ignore duplicates. Below count ... Glad to help but don't forget to check out the link I gave you because the ... How to count duplicates... - microsoft.public.mac.office.excel ...Duplicate column name for OpenRowSet - microsoft.public.sqlserver ... Don't Count Duplicates - microsoft.public.excel.misc ... Duplicate column name for OpenRowSet ... Treeview duplicate nodes (VB.NET 2008) - microsoft.public.dotnet ...When populating a Treeview in code, I am getting "duplicate" nodes. They appear in the treeview, but don't ... For rc As Integer = 0 To (ds1.OrderTree.Count - 1 ... Generate GUID, Find Duplicates, Replace, Rescan - microsoft.public ...Don't Count Duplicates - microsoft.public.excel.misc ... Generate GUID, Find Duplicates, Replace, Rescan - microsoft.public ..... Loop End Sub Here is the script that ... Sum ignoring hidden duplicates - microsoft.public.access.reports ...Don't Count Duplicates - microsoft.public.excel.misc ... Criteria start in E2 and down In F2: =SUM(N ... Count but ignore duplicate data - microsoft.public.excel.misc ... ... How to assign unique number to column duplicates? - microsoft ...I don't know how to do programming, just formulas in excel. Thanks ... How can I count dates if few duplicates in a column - microsoft ... How to assign unique number ... Highlight exact match in column and count - microsoft.public.excel ...The next part I need is to count the number of duplicates (gray shaded & strikethough). I have tried a few VBA but I read they don't work with formula based CF's. Insert copied cells above hidden Named Range - microsoft.public ...... Range("f6").Copy Range("hiddencell").Insert shift:=xlDown End Sub -- Don ... if duplicate goto record - microsoft.public.access Insert copied cells above hidden Named ... Find duplicate, save in a list, delete duplicate using macro ...... Step -1 'size the range for CountIf (don't want to double count ' anything) Set rngResize = rngTemp.Resize(lngCnt, 1) 'test for duplicates ... Don't Count Duplicates - ExcelBanter - Microsoft Excel forum ...Excel Discussion (Misc queries) ... Here's a subset of 3 colunms of data: Zip Code Name County 42125 John Franklin 42156 ... Criteria start in E2 and down In F2: =SUM ... Don't count duplicatesI have a NameField that has duplicates. I want to include a total of the names on my report, but only count the duplicates once. For example: Bill Don't Count Duplicates Excel - Excel Discussion List Tuesday, May ...Here is a subset of 3 colunms of data: Zip Code Name County 42125 John Franklin 42156 John Fairfield 43123 Bill SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicates ...Question Forums > Excel Questions ... Is there anyway to shorten the formula in F4 through F9 or have I got it about a ... It's hard to understand what your question ... Don't count duplicates DataBase - DataBase Discussion List Tuesday ...I have a NameField that has duplicates. I want to include a total of the names on my report, but only count the duplicates once. For example: Bill Bill 7/27/2012 12:48:27 AM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
68 followers. 