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.

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.

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.

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.

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.