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

0 |

1/14/2010 4:23:02 AM

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" <FifthFormula@discussions.microsoft.com> wrote in message news:A3F43870-7596-4B3F-B08E-438F30366624@microsoft.com... > 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

0 |

1/14/2010 5:29:36 AM

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

0 |

1/14/2010 8:32:50 AM

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" <FifthFormula@discussions.microsoft.com> wrote in message > news:A3F43870-7596-4B3F-B08E-438F30366624@microsoft.com... > > 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 > > > . >

0 |

1/15/2010 2:05:01 AM

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" <FifthFormula@discussions.microsoft.com> wrote in message news:EC29FFC7-C702-4932-B3FF-53C301FFDBF9@microsoft.com... > 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" <FifthFormula@discussions.microsoft.com> wrote in message >> news:A3F43870-7596-4B3F-B08E-438F30366624@microsoft.com... >> > 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 >> >> >> . >>

0 |

1/15/2010 2:30:56 AM

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 here: > http://sulprobil.com/html/pfreq.html > > [You might want to hide the second result column ...] > > Regards, > Bernd > . >

0 |

1/16/2010 5:13:17 AM

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" <FifthFormula@discussions.microsoft.com> wrote in message > news:EC29FFC7-C702-4932-B3FF-53C301FFDBF9@microsoft.com... > > 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" <FifthFormula@discussions.microsoft.com> wrote in message > >> news:A3F43870-7596-4B3F-B08E-438F30366624@microsoft.com... > >> > 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 > >> > >> > >> . > >> > > > . >

0 |

1/16/2010 5:36:01 AM

Here's a small sample file that demonstrates this. xFifthFormula.xls 14kb http://cjoint.com/?bqhccNanxU -- Biff Microsoft Excel MVP "FifthFormula" <FifthFormula@discussions.microsoft.com> wrote in message news:D3FC3435-CCD7-40D5-B97F-E0677C17179A@microsoft.com... > 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" <FifthFormula@discussions.microsoft.com> wrote in message >> news:EC29FFC7-C702-4932-B3FF-53C301FFDBF9@microsoft.com... >> > 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" <FifthFormula@discussions.microsoft.com> wrote in >> >> message >> >> news:A3F43870-7596-4B3F-B08E-438F30366624@microsoft.com... >> >> > 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 >> >> >> >> >> >> . >> >> >> >> >> . >>

0 |

1/16/2010 6:04:50 AM

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

0 |

1/16/2010 10:52:26 AM

I am trying to use a formula in conditional format to turn a cell grey if the date in the column is not yet passed. not colored not colored should be grey a b c 10/20/04 10/21/04 10/22/04 5:00 5:00 5:00 =IF(NOW()<a$2,,) Is my formula. Whether I use < or > does not matter, th...

Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

hi How do I count a list of names, some names appear more than once. I only want to count unique names. I use excel 2007 Thanks Cassie One way... =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) -- Biff Microsoft Excel MVP "Cassie" <Cassie@discussions.microsoft.com> wrote in message news:B610A13C-FAF8-46EF-A63C-AAE88EEFB89F@microsoft.com... > hi > How do I count a list of names, some names appear more than once. I only > want to count unique names. > I use excel 2007 > Thanks > Cassie For ...

I have a column of values that is 200 rows tall. The values are eithe a five digit option number or a zero. (I can change the formula tha generates this column to put anything in place of the zeros of tha helps) I need to transfer those option numbers to Work Order in concise list without 175 to 200 zeros. I need to do this automaticall with a formula when the data is imported so my purchasing dept. doe not have to filter the numerous lists with each job with a drop dow menu. I could have all of the zeros on the bottom if I can get all o the five digit option numbers to appear at the top...

Hello, I need hel. I am using this formula =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong with it. I need to find the average for column H in relation to 1(under 2 yrs) in Column E. Hope this makes sense. Thanks, Becky Try: =AVERAGE(IF(E2:E289=1,H2:H289)) Array-enter the formula with CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Becky" <Becky@discussions.microsoft.com> wrote in message news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com... > Hell...

Hi (I'm a newbie so please be patient) I am using excel to build a database for an online store, i have apro 3000 products and each product needs an image path (eg. D:/m docs/images/123.gif). I need a formula that will automatically insert the path to the image in its cell in the image colomn. To make this easier all the image names are the the part number[.gif so the image for product 123 is D:/my docs/images.123.gif. Can any one offer a formula to do this? Code ------------------- Example of spread sheet -------------------------------------------------- Make Model ...

How do I remove changed (no longer relevant) file names from my File list? You can go to Tools--Options and specify the Recently Used File List as 1 (minimum). Press OK and this will then then clear it out so that only the last file opened is in there. Go back to Tools--Options and change that number back to whatever you'd like it to be, for example 4. This will then make it repopulate that list as you open files, up to the number that you specified, in this example 4. HTH, Katherine "Robert W." <anonymous@discussions.microsoft.com> wrote in message news:017f01c39806...

I am about to turn on MC where project accounting is running -- the database has 10 years of data. Does any one know how long running check links will take? AND, will all Projects read as the functional currency following this? I am very concerned about making this change and would like to hear the good and bad if anyone has done it before. Thanks! -- daunheik I would recommend setting up a test server and testing all changes completely. It is a good idea to involve the users in the testing. It is very important to test all changes in GP before applying them to a prod...

Using Excel 2003 Column A is dates. These dates plus 7 are being compared to the current date to determine whether "overdue" gets displayed in Column D. I'm using the following IF statement: If A1+7<today(),"overdue"," " When I copy the formula down, rows that have not yet had data entered into them are all displaying "overdue" because Column A is blank. Do I just need to remember to copy the formula down every time I enter a new row of data, or is there some way I can modify my IF statement to only do the calculation if there is data ...

Hello, I ahve a list of names, with middle initials, that I need to break up. For example the names are formatted like the following: Bayer, Daniel E. I need help with three results: the fist is the first name only; the second is the last name only. And the third, I know I can merge the results for the first and last name, but if there is a formula that will bring out the first and last together I would love to learn what that formula is. Thanks for the help. Jim Text to columns, separate on the comma. then text to columns separate on space. Jim wrote: > Hel...

Hi Everyone, We've just purchased PB 6 and am trying to install it on one of our development stations, but no matter how many times I've installed/ deleted/reinstalled VS2005 and WinCE 6 I cannot for the life of me get it to appear in the project types list for when creating a new solution. It wont even allow me to open an existing project from the demo WinCE 6 folder. I'm now in to my 3rd day of battling with this ! Though the PB icon shows on the splash screen as it starts, as does some of the CE6 'getting started' stuff on the start page. It has had the ...

I'm not sure Excel can do what I want it to do - I have a VLookup formula on my spreadsheet which is returning data, however, I only want it displayed if it returns a specific condition. Is this possible ? -- CMB BT Hi, let' say you want to perform the vlookup if cell A1 is greater than 100, if not will leave the cell blank =if(A1>100,your formula,"") "Claire" wrote: > I'm not sure Excel can do what I want it to do - > > I have a VLookup formula on my spreadsheet which is returning data, however, > I only want it disp...

I am currently running a macro that every time I run the macro I have t replace the name of a subdirectory that a file is saving to. Is ther anyway I can name a cell in ecel that has the subdiectory's name store in it, and get the macro to accept that name in the address that th file is being saved to -- Message posted from http://www.ExcelForum.com Hi, Dim TodaysFolder as String TodaysFolder=cells(1,1).value 'cell A1. You should add the workbook & sheet names to make 'sure it's the right one ThisWorkbook.SaveAs "c:\thisfolder\andthatfloder\" & TodaysFolde...

Hi there, I have setup outlook users that log onto domain A but take their user accounts and exchange server from Domain B. The users log into windows and when they load outlook are prompted for a user name, domain and password. (created in services/exchange server and turning of security) Problem is half of the machines dont remember the user name and domain, and the other half do! its not a version problem cos it happens with 97.04, 97.02 and 2000. I would perfair all users to just have to enter a password can anyone help? ...

where do i find the outgoing and incoming server names to set up an e-mail account? From your ISP. Windows Mail: Setting up an account from start to finish http://windows.microsoft.com/en-US/windows-vista/Windows-Mail-setting-up-an-account-from-start-to-finish -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "lisa law" <lisal@microsoft.com> wrote in message news:e%23ao63%236KHA.356@TK2MSFTNGP05.phx.gbl... > where do i find the outgoing and incoming server names to set up an > e-mail account? You get the correct ma...

Can someone show me how to write the following fragment out, using xmlwriter. Thanks <Message key="comp" xmlns="http://www.xxx.com" xmlns:j="http://www.yyy.com"> </Message> Phil Hunt wrote: > Can someone show me how to write the following fragment out, using > xmlwriter. Thanks > > > > <Message key="comp" xmlns="http://www.xxx.com" > xmlns:j="http://www.yyy.com"> > </Message> Write the 'Message' element in its namespace 'http://www.xxx.com', then write tw...

What type of formula do I need to have a column total values in a row if the cells in the rows value is less then a value in a certain cell? Example Row 2 one has dates in it ( 10-1, 10-2, 10-3, & so on) Cell A1 has today’s date in it =TODAY () So I want Cell J3 to date the values in the cells A3 thru I3 of row 3 if the date above them in row 2 is less then Cell A1- today’s date. Thanks Chance ...

That one works perfectly!! Thanks again! Dank u de veer!! Ja -- jama ----------------------------------------------------------------------- jamaz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1663 View this thread: http://www.excelforum.com/showthread.php?threadid=31504 You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl "jamaz" <jamaz.1fxv5b@excelforum-nospam.com> wrote in message news:jamaz.1fxv5b@excelforum-nospam.com... > > That one works perfectly!! > > Thanks again! > > Dank u de veer!! > >...

Has anyone ever had a Project Server create an second DNS record for a different Computer name associated with the same IP address? I know this is a really strange question, but somehow it actually happened. I am not the Network admin and he swears he did not do it..... Any thoughts? Anyone? Thanks EggHeadCafe - Software Developer Portal of Choice C# Email With Attachment / Upload / Validation http://www.eggheadcafe.com/tutorials/aspnet/d5fd3a93-8c71-4811-94f2-59b545ced4fd/c--email-with-attachment.aspx Project Server gets blamed for a lot of things, but I suspect it's not...

Hi, I'm just getting to grips with Array formulae, and one of the formula I use most frequently is to count combinations of values over two arrays: For example: =SUM(IF(A1:A100="Ford",IF(B1:B100="Focus"1,0))) To give a count of all the Ford Focus in a 2 column list/array (Make & Model in columns A and B respectively) I understand how it works. Creates (and sums) an array of 1s for every combination where there is Ford & Focus. But what I don't understand is how the second IF fits in. Isn't the second IF basically the ELSE clause of the first IF...

I am not sure if I need an IF formula but I am preparing a spreadsheet for work and basically it's a sponsorship form and if the person gets sponsorships of different levels they get a different prize i.e. Up to �10 - Wallet �10.01-�20 Bootbag �20.01-�30 T-shirt etc up to �60 and I would like to enter the amount in column B and then then let the computer to enter either the prize in column C. Can anyone tell me how to do this. I am totally new to excel. Thank you Wendy Hi One way is to use VLOOKUP. First create a table of information: 10 Wallet 20 Bootbag 30 T-Shirt 40 ....

I'd like to change my line numbers to specific salespeople and column names to specific months. Does anyone know if thats possible and how to do it? Thanks Dave, you can't change the ones in excel but you can put your own in row 1 and column A, then you can hide the row and column headers if you want, tools options, view, uncheck row and column headers -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave Bonneville&q...

re: Outlook 2003 When I send e-mail to people, in the FROM FIELD, they only see my complete e-mail address. The e-mail address is NOT preceeded by a "friendly" display name, as one might expect. As per MS Office Online Assistance at http://office.microsoft.com/en-us/assistance/HA011507601033.aspx I have ensured that I have the friendly name I want showing in the YOUR NAME field under USER INFORMATION under TOOLS|E-MAIL ACCOUNTS. This does not solve the problem. I can temporarily solve it, per individual e-mail message, by selecting HAVE REPLIES SENT TO under MESSAGE OPTIONS a...

I want to get my own unique logo. THEN I want to put it permanently into the "logo" box in the Personal Information area so that I can frequently use it. Is this doable? Lots of help here Edit a logo for a personal information set http://office.microsoft.com/en-us/publisher/HP030713481033.aspx?pid=CH062524721033 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Gil Fair" <gilfair@discussions.microsoft.com> wrote in message news:31A9934E-A6D9-4356-91FF-6B14831F2A6E@microsoft.com... >I want to get my own ...

I upgraded a server from GP 8.0 sp5 to GP 9.0 sp2 last weekend. All seems to work fine so far. I have some Crystal Reports that were in the process of being developed and I have questions about 3 Project tables. These tables each have had fields removed. My question is...Where has the data these fields represented been relocated? Has the upgrade taken data from these tables and moved it to other tables? PA10721 -> PA31101 -> PA31102 -> *** Lists the fields added and removed in Project tables *** C:\Program Files\Microsoft Dynamics\GP 9.0 SDK\Content\8.0to9.0\PaTableDetailed_...