CountIF

E

Elizabeth

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
 
J

JulieD

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="")))
 
E

Elizabeth

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
 
J

JulieD

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)
 
B

Bob Phillips

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)
 
N

Nick

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
 
E

Elizabeth

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.
 
J

JulieD

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="")))
 
E

Elizabeth

Thanks that worked.

Nick said:
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
Click to expand...
 
Top