Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Countdiff with more criteria

Hello,

...
Morefunc is one of the better add-ins available so don't let Bernd try to
convince you otherwise.


Definitely.
...


Point is: Morefunc can be as fine as anybody wishes for - without a
license
or without open source code you run the risk that it contains a virus
or that
you lose its functionality in future.

That's why most companies' policies are not allowing to use it (or any
of this kind).

JP obviously intends to use his solution in his company.

So let us not suggest to him to use Morefunc without checking his
company's IT policy.

Whether or not you agree to the number 1 of my Excel Don'ts
http://sulprobil.com/html/excel_don_ts.html
is a stronger but different point: What I cannot use in my company I
won't use at all.

Regards,
Bernd

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Countdiff with more criteria

Hi Bernd,

You have a point with your remark on the company policy. Although not
explicitly forbidden, our IT department will not support it.

Therefore, I used the function that T. Valko suggested.

With kind regards,

JP


"Bernd P" wrote in message
...
Hello,

...
Morefunc is one of the better add-ins available so don't let Bernd try
to
convince you otherwise.


Definitely.
...


Point is: Morefunc can be as fine as anybody wishes for - without a
license
or without open source code you run the risk that it contains a virus
or that
you lose its functionality in future.

That's why most companies' policies are not allowing to use it (or any
of this kind).

JP obviously intends to use his solution in his company.

So let us not suggest to him to use Morefunc without checking his
company's IT policy.

Whether or not you agree to the number 1 of my Excel Don'ts
http://sulprobil.com/html/excel_don_ts.html
is a stronger but different point: What I cannot use in my company I
won't use at all.

Regards,
Bernd



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Countdiff with more criteria

Hello JP,

...
You have a point with your remark on the company policy. Although not
explicitly forbidden, our IT department will not support it.

Therefore, I used the function that T. Valko suggested.
...


Ok, since you ticked the first point off, have a look now at the
second one of my Excel Dont's, please:
http://sulprobil.com/html/w-rule.html

Avoid array formulas you (or the majority of your colleagues) do not
understand or which you will fail to maintain later on.

My thesis is that there are brilliant people around here suggesting
nifty (array) formulas. But 95% (hmm, maybe even 99%) of all Excel
users do not fully understand them, and the (array) formulas are not
tackling the right problem space anyway. A huge bulk of the questions
here can and maybe should be reasonably answered with Pivot tables (I
suggest to search for Herbert Seidenberg's Pivot table solutions
here). And another nice part of the challenges can be solved with VBA
macros which encapsulate the functionality so that the casual user can
easily use them (I suggest to have a look at my website). Before you
"give in" to an array formula I suggest to think about helper columns
and normal worksheet functions.

Maybe you are satisfied with Biff's suggestion for now. But I suggest
to put my suggestion into a cell comment next to his formula. I bet
somebody will struggle with the array formula later on.

Kind regards,
Bernd
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Countdiff with more criteria

Hi Harlan,

Is also a nice alternative. Sorry for late reply.

With kind regards,

JP

"Harlan Grove" wrote in message
...
"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].



  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Countdiff with more criteria

Hi Bernd,

I fully agree not use functions you don't understand and will have a closer
look to your website.


With kind regards,

JP

"Bernd P" wrote in message
...
Hello JP,

...
You have a point with your remark on the company policy. Although not
explicitly forbidden, our IT department will not support it.

Therefore, I used the function that T. Valko suggested.
...


Ok, since you ticked the first point off, have a look now at the
second one of my Excel Dont's, please:
http://sulprobil.com/html/w-rule.html

Avoid array formulas you (or the majority of your colleagues) do not
understand or which you will fail to maintain later on.

My thesis is that there are brilliant people around here suggesting
nifty (array) formulas. But 95% (hmm, maybe even 99%) of all Excel
users do not fully understand them, and the (array) formulas are not
tackling the right problem space anyway. A huge bulk of the questions
here can and maybe should be reasonably answered with Pivot tables (I
suggest to search for Herbert Seidenberg's Pivot table solutions
here). And another nice part of the challenges can be solved with VBA
macros which encapsulate the functionality so that the casual user can
easily use them (I suggest to have a look at my website). Before you
"give in" to an array formula I suggest to think about helper columns
and normal worksheet functions.

Maybe you are satisfied with Biff's suggestion for now. But I suggest
to put my suggestion into a cell comment next to his formula. I bet
somebody will struggle with the array formula later on.

Kind regards,
Bernd





  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countdiff with more criteria

you run the risk that it contains a virus
or that you lose its functionality in future.


And you run that exact same risk using one of your UDFs.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

...
Morefunc is one of the better add-ins available so don't let Bernd try
to
convince you otherwise.


Definitely.
...


Point is: Morefunc can be as fine as anybody wishes for - without a
license
or without open source code you run the risk that it contains a virus
or that
you lose its functionality in future.

That's why most companies' policies are not allowing to use it (or any
of this kind).

JP obviously intends to use his solution in his company.

So let us not suggest to him to use Morefunc without checking his
company's IT policy.

Whether or not you agree to the number 1 of my Excel Don'ts
http://sulprobil.com/html/excel_don_ts.html
is a stronger but different point: What I cannot use in my company I
won't use at all.

Regards,
Bernd



  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countdiff with more criteria

So, if you don't understand a "complex" array formula what are the chances
that you're going to understand VBA code?

I suggest to search for Herbert Seidenberg's Pivot table solutions


His solution to *everything* is a pt and he only posts solutions for Excel
2007. What about the people that don't use Excel 2007? I guess they're SOL.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello JP,

...
You have a point with your remark on the company policy. Although not
explicitly forbidden, our IT department will not support it.

Therefore, I used the function that T. Valko suggested.
...


Ok, since you ticked the first point off, have a look now at the
second one of my Excel Dont's, please:
http://sulprobil.com/html/w-rule.html

Avoid array formulas you (or the majority of your colleagues) do not
understand or which you will fail to maintain later on.

My thesis is that there are brilliant people around here suggesting
nifty (array) formulas. But 95% (hmm, maybe even 99%) of all Excel
users do not fully understand them, and the (array) formulas are not
tackling the right problem space anyway. A huge bulk of the questions
here can and maybe should be reasonably answered with Pivot tables (I
suggest to search for Herbert Seidenberg's Pivot table solutions
here). And another nice part of the challenges can be solved with VBA
macros which encapsulate the functionality so that the casual user can
easily use them (I suggest to have a look at my website). Before you
"give in" to an array formula I suggest to think about helper columns
and normal worksheet functions.

Maybe you are satisfied with Biff's suggestion for now. But I suggest
to put my suggestion into a cell comment next to his formula. I bet
somebody will struggle with the array formula later on.

Kind regards,
Bernd



  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Countdiff with more criteria

Hello Biff,

...
you run the risk that it contains a virus
or that you lose its functionality in future.


And you run that exact same risk using one of your UDFs.


No. My UDFs are open source, fairly short, and a competent third
person could easily adapt them to any future Excel version, I honestly
believe.

...
So, if you don't understand a "complex" array formula what are the chances
that you're going to understand VBA code?


You do not need to understand to program or to maintain them, you need
to know how to use them - that's encapsulation.

...
His solution to *everything* is a pt and he only posts solutions for Excel
2007. What about the people that don't use Excel 2007? I guess they're SOL.
...


Not to *everything*. But have a look at
http://sulprobil.com/html/pfreq.html
(look at the "statistic on engineers" example, please). You can see
that yours and Harlan's approach is about 6x faster than my Pstat
approach for 100 data records. But for 1,000 data records my VBA
approach is already about 5x faster than yours and Harlan's (who's
falling over if there is any empty record, btw). And the approach
which I deem to be state-of-the-art is another 3x faster:
Prepare the data with Pfreq and then apply a Pivot table. You can see
all variants in my sample file(s).

Biff, I like your and Harlan's clever solutions and I think I learned
a lot from both of you. But for many Excel challenges a Pivot table
seems to be a preferrable solution.

Btw: I think its not fair to say that I only post Excel 2007
solutions. I publish my worksheet formulas and my VBA macros which can
be used in earlier versions as well.

Regards,
Bernd

  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countdiff with more criteria

Btw: I think its not fair to say that I only post Excel 2007 solutions.

Who said that?

My response was directed at this statement:

I suggest to search for Herbert Seidenberg's Pivot table solutions


*Every* post made by Herbert Seidenberg is a 2007 pt.

I disagree with just about everything you said. You're not going to change
my mind and I'm not going to change your mind so....

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello Biff,

...
you run the risk that it contains a virus
or that you lose its functionality in future.


And you run that exact same risk using one of your UDFs.


No. My UDFs are open source, fairly short, and a competent third
person could easily adapt them to any future Excel version, I honestly
believe.

...
So, if you don't understand a "complex" array formula what are the
chances
that you're going to understand VBA code?


You do not need to understand to program or to maintain them, you need
to know how to use them - that's encapsulation.

...
His solution to *everything* is a pt and he only posts solutions for
Excel
2007. What about the people that don't use Excel 2007? I guess they're
SOL.
...


Not to *everything*. But have a look at
http://sulprobil.com/html/pfreq.html
(look at the "statistic on engineers" example, please). You can see
that yours and Harlan's approach is about 6x faster than my Pstat
approach for 100 data records. But for 1,000 data records my VBA
approach is already about 5x faster than yours and Harlan's (who's
falling over if there is any empty record, btw). And the approach
which I deem to be state-of-the-art is another 3x faster:
Prepare the data with Pfreq and then apply a Pivot table. You can see
all variants in my sample file(s).

Biff, I like your and Harlan's clever solutions and I think I learned
a lot from both of you. But for many Excel challenges a Pivot table
seems to be a preferrable solution.

Btw: I think its not fair to say that I only post Excel 2007
solutions. I publish my worksheet formulas and my VBA macros which can
be used in earlier versions as well.

Regards,
Bernd



  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Countdiff with more criteria

Hello Biff,

Fair enough.

It's easy for me to watch out for COUNT(1/FREQUENCY()), SUM(...
(FREQUENCY()) or SUMPROUCT(()/COUNTIF()) suggestions and to point out
what Charles already stated some time ago. I cite him at:
http://sulprobil.com/html/count_unique.html

There are no useless posts. They can still serve as a bad example.

Regards,
Bernd


  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Countdiff with more criteria

Hi Biff, Bernd,

Not in my intention to start a discussion overhere.

You are both of great help for Excel users in need.

De gustibus et coloribus, non disputandem est.

(Do not discuss about colours and tastes)

With kind regards,

JP




"T. Valko" wrote in message
...
you run the risk that it contains a virus
or that you lose its functionality in future.


And you run that exact same risk using one of your UDFs.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

...
Morefunc is one of the better add-ins available so don't let Bernd try
to
convince you otherwise.

Definitely.
...


Point is: Morefunc can be as fine as anybody wishes for - without a
license
or without open source code you run the risk that it contains a virus
or that
you lose its functionality in future.

That's why most companies' policies are not allowing to use it (or any
of this kind).

JP obviously intends to use his solution in his company.

So let us not suggest to him to use Morefunc without checking his
company's IT policy.

Whether or not you agree to the number 1 of my Excel Don'ts
http://sulprobil.com/html/excel_don_ts.html
is a stronger but different point: What I cannot use in my company I
won't use at all.

Regards,
Bernd





  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countdiff with more criteria

Bernd always does this. It's no big deal.

Thanks for your feedback!

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
Hi Biff, Bernd,

Not in my intention to start a discussion overhere.

You are both of great help for Excel users in need.

De gustibus et coloribus, non disputandem est.

(Do not discuss about colours and tastes)

With kind regards,

JP




"T. Valko" wrote in message
...
you run the risk that it contains a virus
or that you lose its functionality in future.


And you run that exact same risk using one of your UDFs.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

...
Morefunc is one of the better add-ins available so don't let Bernd try
to
convince you otherwise.

Definitely.
...

Point is: Morefunc can be as fine as anybody wishes for - without a
license
or without open source code you run the risk that it contains a virus
or that
you lose its functionality in future.

That's why most companies' policies are not allowing to use it (or any
of this kind).

JP obviously intends to use his solution in his company.

So let us not suggest to him to use Morefunc without checking his
company's IT policy.

Whether or not you agree to the number 1 of my Excel Don'ts
http://sulprobil.com/html/excel_don_ts.html
is a stronger but different point: What I cannot use in my company I
won't use at all.

Regards,
Bernd







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 12:51 PM.

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

About Us

"It's about Microsoft Excel"