Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |