| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Counting of Duplicated Names
I have names in A1:A10 like this:
Ted, Ted, Ted, Mark, David, David, Bonnie, Bonnie, Bonnie, Fred I have their department assignments in B1:B10 like this: EE,EE,EE,ME,CE,CE,ME,ME,ME,EE I am using the following array formula to count the total number of employees: {=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))} This yields a total of 5 employees. But I'd like to modify the formula to count the number of employees by department. In other words, I'd like the formula to return that I have 2 employees in ME, 2 in EE and 1 in CE. Thanks in advance for the help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Counting of Duplicated Names
Try this array formula** :
D1 = EE D2 = ME D3 = CE Entered in E1: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A1)+1),1)) Copy down to E3 ** 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 "jkiser" wrote in message ... I have names in A1:A10 like this: Ted, Ted, Ted, Mark, David, David, Bonnie, Bonnie, Bonnie, Fred I have their department assignments in B1:B10 like this: EE,EE,EE,ME,CE,CE,ME,ME,ME,EE I am using the following array formula to count the total number of employees: {=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))} This yields a total of 5 employees. But I'd like to modify the formula to count the number of employees by department. In other words, I'd like the formula to return that I have 2 employees in ME, 2 in EE and 1 in CE. Thanks in advance for the help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Counting of Duplicated Names
Ooops! Typo
I forgot to make the last ROW function absolute. Correct formula should be: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A$1)+1),1)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : D1 = EE D2 = ME D3 = CE Entered in E1: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A1)+1),1)) Copy down to E3 ** 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 "jkiser" wrote in message ... I have names in A1:A10 like this: Ted, Ted, Ted, Mark, David, David, Bonnie, Bonnie, Bonnie, Fred I have their department assignments in B1:B10 like this: EE,EE,EE,ME,CE,CE,ME,ME,ME,EE I am using the following array formula to count the total number of employees: {=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))} This yields a total of 5 employees. But I'd like to modify the formula to count the number of employees by department. In other words, I'd like the formula to return that I have 2 employees in ME, 2 in EE and 1 in CE. Thanks in advance for the help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Counting of Duplicated Names
Thanks....I'm going to try it and see if it works.
BTW....I was able to do it with the following formula.... {=SUMPRODUCT(--(('AY-09'!A1:A10<"")/(COUNTIF(A1:A10,A1:A10)+(A1:A10=""))),--(B1:B10="CSE"))} This excludes any BLANK cells. It also gives a fractional number that is very, very close and is correct if I round it. "T. Valko" wrote: Ooops! Typo I forgot to make the last ROW function absolute. Correct formula should be: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A$1)+1),1)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : D1 = EE D2 = ME D3 = CE Entered in E1: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A1)+1),1)) Copy down to E3 ** 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 "jkiser" wrote in message ... I have names in A1:A10 like this: Ted, Ted, Ted, Mark, David, David, Bonnie, Bonnie, Bonnie, Fred I have their department assignments in B1:B10 like this: EE,EE,EE,ME,CE,CE,ME,ME,ME,EE I am using the following array formula to count the total number of employees: {=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))} This yields a total of 5 employees. But I'd like to modify the formula to count the number of employees by department. In other words, I'd like the formula to return that I have 2 employees in ME, 2 in EE and 1 in CE. Thanks in advance for the help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Counting of Duplicated Names
It also gives a fractional number that is very,
very close and is correct if I round it. In other words, it returns an incorrect result and rounding makes it seem correct. Rounding won't work because when the fraction is on the opposite end of what you're rounding for you'll still get an incorrect result. For eample, 3.3 rounded to the nearest whole number would be 3 which might be correct but it's just "dumb luck". If the result was 3.5 that gets rounded to 4 and now your count is off by 1. A unique count can't be a fraction! You don't have 3.3 unique employees! (or do you? <g) This excludes any BLANK cells If you have empty cells: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,IF(A$1:A$10<"",M ATCH(A$1:A$10,A$1:A$10,0))),ROW(A$1:A$10)-ROW(A$1)+1),1)) -- Biff Microsoft Excel MVP "jkiser" wrote in message ... Thanks....I'm going to try it and see if it works. BTW....I was able to do it with the following formula.... {=SUMPRODUCT(--(('AY-09'!A1:A10<"")/(COUNTIF(A1:A10,A1:A10)+(A1:A10=""))),--(B1:B10="CSE"))} This excludes any BLANK cells. It also gives a fractional number that is very, very close and is correct if I round it. "T. Valko" wrote: Ooops! Typo I forgot to make the last ROW function absolute. Correct formula should be: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A$1)+1),1)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : D1 = EE D2 = ME D3 = CE Entered in E1: =SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A1)+1),1)) Copy down to E3 ** 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 "jkiser" wrote in message ... I have names in A1:A10 like this: Ted, Ted, Ted, Mark, David, David, Bonnie, Bonnie, Bonnie, Fred I have their department assignments in B1:B10 like this: EE,EE,EE,ME,CE,CE,ME,ME,ME,EE I am using the following array formula to count the total number of employees: {=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))} This yields a total of 5 employees. But I'd like to modify the formula to count the number of employees by department. In other words, I'd like the formula to return that I have 2 employees in ME, 2 in EE and 1 in CE. Thanks in advance for the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Months are being duplicated in a conditional if statements | Excel Worksheet Functions | |||
| Duplicated Names In List | Excel Discussion (Misc queries) | |||
| Have 2 columns of names - need to filter out names not duplicated | Excel Worksheet Functions | |||
| Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
| HOW DO I REMOVE DUPLICATED NAMES ON SPREADSHEET EXCEL97 3900 ROWS. | Excel Worksheet Functions | |||
Linear Mode
