Home 
Search 
Today's Posts 
#1




Countdiff with more criteria
Hi All,
I would like to count a sort of diversity per person and per month. Sample data: Customer Engineer Date A JP 01/11/2009 A Bill 01/11/2009 A Bill 01/11/2009 A JP 01/11/2009 B JP 01/11/2009 A JP 01/12/2009 B Bill 01/12/2009 C Bill 01/12/2009 D Bill 01/12/2009 E Bill 01/12/2009 In November, JP has served customers A & B, Bill only for A. The result for JP is 2 while for Bill, it is 1. In December: JP=1, Bill = 4. With {=COUNTDIFF((A2:A11)&(B2:B11))} I am already able to calculate over the whole range the differing combinations, but how to include the criteria person and month? Any help will be very appreciated. With kind regards, JP 
#2




Countdiff with more criteria
Which version of Excel has this COUNTDIFF function, or is it a 3rd part
addin?  David Biddulph "JP Ronse" wrote in message ... Hi All, I would like to count a sort of diversity per person and per month. Sample data: Customer Engineer Date A JP 01/11/2009 A Bill 01/11/2009 A Bill 01/11/2009 A JP 01/11/2009 B JP 01/11/2009 A JP 01/12/2009 B Bill 01/12/2009 C Bill 01/12/2009 D Bill 01/12/2009 E Bill 01/12/2009 In November, JP has served customers A & B, Bill only for A. The result for JP is 2 while for Bill, it is 1. In December: JP=1, Bill = 4. With {=COUNTDIFF((A2:A11)&(B2:B11))} I am already able to calculate over the whole range the differing combinations, but how to include the criteria person and month? Any help will be very appreciated. With kind regards, JP 
#3




Countdiff with more criteria
Hello JP,
If you do not want to use a Pivot Table select a sufficiently long range with two columns and arrayenter =Pstat("count",TEXT(C1:C999,"YYYYMM")="200912",B1: B999,A1:A999) Pstat you can find he http://sulprobil.com/html/pstat.html Regards, Bernd 
#4




Countdiff with more criteria
Hi David,
You are right, it is included in MoreFunc for Excel (Morefunc is a freeware library of 67 new worksheet functions for Excel, created and developped by Laurent Longre). So, I better do not use this function if I want to share the workbook with the colleagues. Good remark. With kind regards, JP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Which version of Excel has this COUNTDIFF function, or is it a 3rd part addin?  David Biddulph "JP Ronse" wrote in message ... Hi All, I would like to count a sort of diversity per person and per month. Sample data: Customer Engineer Date A JP 01/11/2009 A Bill 01/11/2009 A Bill 01/11/2009 A JP 01/11/2009 B JP 01/11/2009 A JP 01/12/2009 B Bill 01/12/2009 C Bill 01/12/2009 D Bill 01/12/2009 E Bill 01/12/2009 In November, JP has served customers A & B, Bill only for A. The result for JP is 2 while for Bill, it is 1. In December: JP=1, Bill = 4. With {=COUNTDIFF((A2:A11)&(B2:B11))} I am already able to calculate over the whole range the differing combinations, but how to include the criteria person and month? Any help will be very appreciated. With kind regards, JP 
#5




Countdiff with more criteria
Hi Bernd,
A pivot table is not an option in this project as the figures are for the management only, the employees are allowed to consult their own figures but not the ones of their colleagues. I know that you have written a lot of very useful functions but as most of the colleagues are Excel dummies, I prefer to work it out with the standard Excel functions, if possible. With kind regards, JP "Bernd P" wrote in message ... Hello JP, If you do not want to use a Pivot Table select a sufficiently long range with two columns and arrayenter =Pstat("count",TEXT(C1:C999,"YYYYMM")="200912",B1: B999,A1:A999) Pstat you can find he http://sulprobil.com/html/pstat.html Regards, Bernd 
#6




Countdiff with more criteria
You can embed the Morefunc addin into the file if you're using a version of
Excel prior to Excel 2007. I'm not sure about doing it with Excel 2007. That way, if you distribute the file the recipients don't need to have the addin installed on their machine. Morefunc is one of the better addins available so don't let Bernd try to convince you otherwise. I prefer to work it out with the standard Excel functions, if possible Try this array formula** ... Data in the range A2:C11 In the formula: Cust (customer) refers to Sheet1!$A$2:$A$11 Eng (engineer) refers to Sheet1!$B$2:$B$11 Dates refers to Sheet1!$C$2:$C$11 I'm assuming the dates are all within the same year so we're only going to test the dates for the month. E2:E3 are the 1st of the month dates 1/11/2009, 1/12/2009 F1:G1 = JP, Bill Enter this array formula in F2: =SUM(IF(FREQUENCY(IF(Eng=F$1,IF(MONTH(Dates)=MONTH ($E2),MATCH(Cust,Cust,0))),ROW(Cust)MIN(ROW(Cust))+1),1)) ** 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. Copy across to G2 then down to F3:G3 Assumes there are no empty cells in the Customer range otherwise you could get errors.  Biff Microsoft Excel MVP "JP Ronse" wrote in message ... Hi Bernd, A pivot table is not an option in this project as the figures are for the management only, the employees are allowed to consult their own figures but not the ones of their colleagues. I know that you have written a lot of very useful functions but as most of the colleagues are Excel dummies, I prefer to work it out with the standard Excel functions, if possible. With kind regards, JP "Bernd P" wrote in message ... Hello JP, If you do not want to use a Pivot Table select a sufficiently long range with two columns and arrayenter =Pstat("count",TEXT(C1:C999,"YYYYMM")="200912",B1: B999,A1:A999) Pstat you can find he http://sulprobil.com/html/pstat.html Regards, Bernd 
#7




Countdiff with more criteria
Hi,
Your function calculates exactly what I need. Many thanks for your assistance and your time. With kind regards, JP "T. Valko" wrote in message ... You can embed the Morefunc addin into the file if you're using a version of Excel prior to Excel 2007. I'm not sure about doing it with Excel 2007. That way, if you distribute the file the recipients don't need to have the addin installed on their machine. Morefunc is one of the better addins available so don't let Bernd try to convince you otherwise. I prefer to work it out with the standard Excel functions, if possible Try this array formula** ... Data in the range A2:C11 In the formula: Cust (customer) refers to Sheet1!$A$2:$A$11 Eng (engineer) refers to Sheet1!$B$2:$B$11 Dates refers to Sheet1!$C$2:$C$11 I'm assuming the dates are all within the same year so we're only going to test the dates for the month. E2:E3 are the 1st of the month dates 1/11/2009, 1/12/2009 F1:G1 = JP, Bill Enter this array formula in F2: =SUM(IF(FREQUENCY(IF(Eng=F$1,IF(MONTH(Dates)=MONTH ($E2),MATCH(Cust,Cust,0))),ROW(Cust)MIN(ROW(Cust))+1),1)) ** 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. Copy across to G2 then down to F3:G3 Assumes there are no empty cells in the Customer range otherwise you could get errors.  Biff Microsoft Excel MVP "JP Ronse" wrote in message ... Hi Bernd, A pivot table is not an option in this project as the figures are for the management only, the employees are allowed to consult their own figures but not the ones of their colleagues. I know that you have written a lot of very useful functions but as most of the colleagues are Excel dummies, I prefer to work it out with the standard Excel functions, if possible. With kind regards, JP "Bernd P" wrote in message ... Hello JP, If you do not want to use a Pivot Table select a sufficiently long range with two columns and arrayenter =Pstat("count",TEXT(C1:C999,"YYYYMM")="200912",B1: B999,A1:A999) Pstat you can find he http://sulprobil.com/html/pstat.html Regards, Bernd 
#8




Countdiff with more criteria
You're welcome. Thanks for the feedback!
 Biff Microsoft Excel MVP "JP Ronse" wrote in message ... Hi, Your function calculates exactly what I need. Many thanks for your assistance and your time. With kind regards, JP "T. Valko" wrote in message ... You can embed the Morefunc addin into the file if you're using a version of Excel prior to Excel 2007. I'm not sure about doing it with Excel 2007. That way, if you distribute the file the recipients don't need to have the addin installed on their machine. Morefunc is one of the better addins available so don't let Bernd try to convince you otherwise. I prefer to work it out with the standard Excel functions, if possible Try this array formula** ... Data in the range A2:C11 In the formula: Cust (customer) refers to Sheet1!$A$2:$A$11 Eng (engineer) refers to Sheet1!$B$2:$B$11 Dates refers to Sheet1!$C$2:$C$11 I'm assuming the dates are all within the same year so we're only going to test the dates for the month. E2:E3 are the 1st of the month dates 1/11/2009, 1/12/2009 F1:G1 = JP, Bill Enter this array formula in F2: =SUM(IF(FREQUENCY(IF(Eng=F$1,IF(MONTH(Dates)=MONTH ($E2),MATCH(Cust,Cust,0))),ROW(Cust)MIN(ROW(Cust))+1),1)) ** 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. Copy across to G2 then down to F3:G3 Assumes there are no empty cells in the Customer range otherwise you could get errors.  Biff Microsoft Excel MVP "JP Ronse" wrote in message ... Hi Bernd, A pivot table is not an option in this project as the figures are for the management only, the employees are allowed to consult their own figures but not the ones of their colleagues. I know that you have written a lot of very useful functions but as most of the colleagues are Excel dummies, I prefer to work it out with the standard Excel functions, if possible. With kind regards, JP "Bernd P" wrote in message ... Hello JP, If you do not want to use a Pivot Table select a sufficiently long range with two columns and arrayenter =Pstat("count",TEXT(C1:C999,"YYYYMM")="200912",B1: B999,A1:A999) Pstat you can find he http://sulprobil.com/html/pstat.html Regards, Bernd 
#9




Countdiff with more criteria
"T. Valko" wrote...
.... Morefunc is one of the better addins available so don't let Bernd try to convince you otherwise. Definitely. Cust (customer) refers to Sheet1!$A$2:$A$11 Eng (engineer) refers to Sheet1!$B$2:$B$11 Dates refers to Sheet1!$C$2:$C$11 I'm assuming the dates are all within the same year so we're only going to test the dates for the month. E2:E3 are the 1st of the month dates 1/11/2009, 1/12/2009 F1:G1 = JP, Bill If there would be multiple result formulas, it may be better to define a 4th name BIN referring to the formula =ROW(Cust)MIN(ROW(Cust)) (omission of +1 intentional). The you could use formulas like =COUNT(1/FREQUENCY(MATCH(Cust,Cust,0)*(Eng=F$1)*(MONTH(Date s)=MONTH ($E2)),Bin))1 which doesn't have to be entered as an array formula [because it doesn't call IF, and FREQUENCY always returns an array, so its results are handled like array constants]. 
#10




Countdiff with more criteria
On Sat, 12 Dec 2009 14:27:51 +0100, "JP Ronse" wrote:
A pivot table is not an option in this project as the figures are for the management only, the employees are allowed to consult their own figures but not the ones of their colleagues. You could present a filtered pivot table to the employees, showing only their own information. And also present an unfiltered table to management. ron 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Query criteria based on a changing criteria list  Excel Discussion (Misc queries)  
Count how many criteria in a column match criteria in another colu  Excel Discussion (Misc queries)  
EXCEL  Meet 2 criteria, then find next case of third criteria  Excel Worksheet Functions  
Counting Cells with multiple criteria.One criteria supporting wild  Excel Worksheet Functions  
Counting Cells with multiple criteria.One criteria supporting wild  Excel Worksheet Functions 