CountIF
 

I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Hi Elizabeth

if you want a count per Project Manager use the following formula
=SUMPRODUCT(--($B$11:$B$19=F11)/(COUNTIF($A$11:$A$19,$A$11:$A$19)+($A$11:$A$19="") ))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

In additional to number of sales order I need to know how many each Project
Mgr has assigned to them. E.g. I need to see that
Prem 3
DeVivo 1
Sturm 1

Please help

"Elizabeth" wrote:

I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Hi Elizabeth

sorry, let's try that again ...

if you want a count per Project Manager use the following formula
=SUMPRODUCT(--($B$11:$B$19=F11)/(COUNTIF($A$11:$A$19,$A$11:$A$19)+($A$11:$A$19="") ))
where your current data is in the range A11:B19
and you have "Gomes" typed into F11

if you want an overall count of unique project numbers use the following
array formula
=SUM(1/COUNTIF(A11:A19,A11:A19))
(an array formula needs to be entered using control & shift & enter not just
enter)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Hi Elizabeth,

Here is a formula that I first saw from Domenic

=SUM(IF(FREQUENCY(IF((A2:A20<"")*(B2:B20="Gomes") ,A2:A20),IF((A2:A20<"")*(
B2:B20="Gomes"),A2:A20))0,1))

it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Hi Elizabeth

Assuming your list of slaes orders in in column A and the name of the
Project manager is in B and that the sales order is unique to a project
manager then you can use this formula.

=SUM(IF($B$1:$B$12=E1,1/COUNTIF($A$1:$A$12,$A$1:$A$12),0))

Cell E1 contains the name of the Manager. Copy this fromula in to cell F1
and enter as an Array formula (Crll+Shift+Enter)
Enter the names in E2 etc and copy down to F2 etc.

Nick




"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Julie, I need to see count of sales order per project manager. I think I
must doing something wrong. I gave error message using this formula:
=SUMPRODUCT(--($A$3:$A$75=N3)/COUNTIF($D$3:$D$75,$D$3:$D$75)+($A$3:$A$75="")))

A3:A75 Sales Order range
D3:D75 Project Manager
N3 where I want the results displayed.

I think I missed something. Help.
"JulieD" wrote:

Hi Elizabeth

sorry, let's try that again ...

if you want a count per Project Manager use the following formula
=SUMPRODUCT(--($B$11:$B$19=F11)/(COUNTIF($A$11:$A$19,$A$11:$A$19)+($A$11:$A$19="") ))
where your current data is in the range A11:B19
and you have "Gomes" typed into F11

if you want an overall count of unique project numbers use the following
array formula
=SUM(1/COUNTIF(A11:A19,A11:A19))
(an array formula needs to be entered using control & shift & enter not just
enter)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Hi

N3 needs to contain the project manager's name
then edit the formula as
=SUMPRODUCT(--($D$3:$D$75=N3)/COUNTIF($A$3:$A$75,$A$3:$A$75)+($A$3:$A$75="")))
or put the project manager's name in M3 and use
=SUMPRODUCT(--($D$3:$D$75=M3)/COUNTIF($A$3:$A$75,$A$3:$A$75)+($A$3:$A$75="")))
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Elizabeth" wrote in message
...
Julie, I need to see count of sales order per project manager. I think I
must doing something wrong. I gave error message using this formula:
=SUMPRODUCT(--($A$3:$A$75=N3)/COUNTIF($D$3:$D$75,$D$3:$D$75)+($A$3:$A$75="")))

A3:A75 Sales Order range
D3:D75 Project Manager
N3 where I want the results displayed.

I think I missed something. Help.
"JulieD" wrote:

Hi Elizabeth

sorry, let's try that again ...

if you want a count per Project Manager use the following formula
=SUMPRODUCT(--($B$11:$B$19=F11)/(COUNTIF($A$11:$A$19,$A$11:$A$19)+($A$11:$A$19="") ))
where your current data is in the range A11:B19
and you have "Gomes" typed into F11

if you want an overall count of unique project numbers use the following
array formula
=SUM(1/COUNTIF(A11:A19,A11:A19))
(an array formula needs to be entered using control & shift & enter not
just
enter)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use
the
CountIF so that it only counts the number once? Here is an example of
my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm

Thanks that worked.

"Nick" wrote:

Hi Elizabeth

Assuming your list of slaes orders in in column A and the name of the
Project manager is in B and that the sales order is unique to a project
manager then you can use this formula.

=SUM(IF($B$1:$B$12=E1,1/COUNTIF($A$1:$A$12,$A$1:$A$12),0))

Cell E1 contains the name of the Manager. Copy this fromula in to cell F1
and enter as an Array formula (Crll+Shift+Enter)
Enter the names in E2 etc and copy down to F2 etc.

Nick




"Elizabeth" wrote in message
...
I have a list of records where the Project Manager has several numbers
assigned to him but sometimes the number is duplicated. How do I use the
CountIF so that it only counts the number once? Here is an example of my
data:

Sales Order Project Mgr
6100517416 Gomes
6100517416 Gomes
6100517424 Gomes
6100517424 Gomes
6100517425 Gomes
6100517425 Gomes
6100521493 DeVivo
6100531944 Sturm
6100531944 Sturm