A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Countdiff with more criteria



 
 
Thread Tools Display Modes
  #1  
Old December 12th 09, 01:53 PM posted to microsoft.public.excel.worksheet.functions
JP Ronse
external usenet poster
 
Posts: 174
Default 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


Ads
  #2  
Old December 12th 09, 02:06 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default Countdiff with more criteria

Which version of Excel has this COUNTDIFF function, or is it a 3rd part
add-in?
--
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  
Old December 12th 09, 02:11 PM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 806
Default 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 array-enter
=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  
Old December 12th 09, 02:23 PM posted to microsoft.public.excel.worksheet.functions
JP Ronse
external usenet poster
 
Posts: 174
Default 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
> add-in?
> --
> 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  
Old December 12th 09, 02:27 PM posted to microsoft.public.excel.worksheet.functions
JP Ronse
external usenet poster
 
Posts: 174
Default 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 array-enter
> =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  
Old December 12th 09, 06:27 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default Countdiff with more criteria

You can embed the Morefunc add-in 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 add-in
installed on their machine.

Morefunc is one of the better add-ins 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 array-enter
>> =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  
Old December 13th 09, 11:48 AM posted to microsoft.public.excel.worksheet.functions
JP Ronse
external usenet poster
 
Posts: 174
Default 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 add-in 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
> add-in installed on their machine.
>
> Morefunc is one of the better add-ins 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 array-enter
>>> =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  
Old December 13th 09, 06:06 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default 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 add-in 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 add-in installed on their machine.
>>
>> Morefunc is one of the better add-ins 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 array-enter
>>>> =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  
Old December 14th 09, 03:44 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,230
Default Countdiff with more criteria

"T. Valko" > wrote...
....
>Morefunc is one of the better add-ins 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  
Old December 14th 09, 04:41 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query criteria based on a changing criteria list bwilk77 Excel Discussion (Misc queries) 2 May 27th 09 04:03 PM
Count how many criteria in a column match criteria in another colu Charles Stover Excel Discussion (Misc queries) 3 March 6th 09 09:39 PM
EXCEL - Meet 2 criteria, then find next case of third criteria Elaine Excel Worksheet Functions 3 December 1st 08 11:43 PM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Saleem Excel Worksheet Functions 1 January 12th 05 11:54 AM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Arain Excel Worksheet Functions 1 January 12th 05 09:33 AM


All times are GMT +1. The time now is 05:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.