Unique project names with complex array formulas

[FifthFormula]

Project Approved Type TypeSummary "HowManyProj"
-------- ----------- ----- ---------------- ----------------
Green 1/5/08 Charity Charity 3
Green 1/5/09 Profit Profit 2
Blue 1/7/09 Neutral Neutral 1
White 1/7/08 Profit
Red Charity
Blue 1/15/09 Charity
Green Profit
Yellow 1/16/08 Charity
Green Charity

Manual answer is "How Many Projects"
How do I develop a formula that will tell me "How Many Unique Project Names"
of the "Charity" type have an "Approval Date"(3, Green, Blue, Yellow)
How many unique of the "Profit" type have an approval date (2, Green, White)
And how many unique of the "Neutral" type have an approval date (1, blue)
Quite a challenge.

 

[T. Valko]

So, if your last row had a date it wouldn't be counted because "Green -
Charity" already has another entry with a date?

 

[Bernd P]

Hello Jorge,

If you do not like pivot tables I suggest to select a sufficiently
long area with three columns and to array-enter:
=Pfreq(C2:C10,ISNUMBER(B2:B10))

My UDF Pfreq you can find here:
http://sulprobil.com/html/pfreq.html

[You might want to hide the second result column ...]

Regards,
Bernd

 

[FifthFormula]

T.Valko, yes, that is correct. If last row had an approval date, it would not
be counted. Only need the unique name of the approved projects for each type.
Thanks. Jorge.R

 

[T. Valko]

Try this...

Assuming data in the range A2:C10.

E2:E4 = Charity. Profit, Neutral

Enter this array formula** in F2 and copy down to F4:

=SUM(IF(FREQUENCY(IF(C$2:C$10=E2,IF(ISNUMBER(B$2:B$10),MATCH(A$2:A$10,A$2:A$10,0))),ROW(A$2:A$10)-ROW(A$2)+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.

Assumes no empty cells in the project name range (A2:A10).

 

[FifthFormula]

Many thanks Bernd P. I'm not crazy about pivot tables, I like formulas better.

 

[FifthFormula]

Thank you T.Valko. On first try, I could not get the expected results, but
I'm at least getting some numbers back. Will try over the weekend.

 

[T. Valko]

Here's a small sample file that demonstrates this.

xFifthFormula.xls 14kb

http://cjoint.com/?bqhccNanxU

 

[Bernd P]

Hello,

Having seen Biff's example I saw that I needed to change my formula:
=Pfreq(Pstat("Count",ISNUMBER(B2:B20),C2:C20,A2:A20))

A sample file which shows both Biff's and my approach you can find at:
http://sulprobil.com/html/pfreq.html
its the 208k Excel 2003 sample file.

Regards,
Bernd