| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
| Complex Function: Match names on Two Sheets | Excel Worksheet Functions | |||
| Finding unique names--then converting those names to unique number | Excel Discussion (Misc queries) | |||
| Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
| Help with complex index array issue | Excel Worksheet Functions | |||
Linear Mode
