View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
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