Unique project names with complex array formulas
 

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.
--
Jorge.R

Unique project names with complex array formulas
 

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

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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.
--
Jorge.R

Unique project names with complex array formulas
 

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 he
http://sulprobil.com/html/pfreq.html

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

Regards,
Bernd

Unique project names with complex array formulas
 

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
--
Jorge.R


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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.
--
Jorge.R


.

Unique project names with complex array formulas
 

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).

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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
--
Jorge.R


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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.
--
Jorge.R


.

Unique project names with complex array formulas
 

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


"Bernd P" wrote:

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 he
http://sulprobil.com/html/pfreq.html

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

Regards,
Bernd
.

Unique project names with complex array formulas
 

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.
--
Jorge.R


"T. Valko" wrote:

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).

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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
--
Jorge.R


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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.
--
Jorge.R


.



.

Unique project names with complex array formulas
 

Here's a small sample file that demonstrates this.

xFifthFormula.xls 14kb

http://cjoint.com/?bqhccNanxU

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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.
--
Jorge.R


"T. Valko" wrote:

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).

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in message
...
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
--
Jorge.R


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"FifthFormula" wrote in
message
...
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.
--
Jorge.R


.



.

Unique project names with complex array formulas
 

Hello,

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

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