Nested Formula for Management Report
Posted by Rich R on March 23, 2001 7:10 AM
Help!
I'm managing a spreadsheet for a particular project at work. I have been charged with producing a weekly management report which will show which portion of a project has been started and which have not. I'm trying to perform a count based on data in 2 columns. For example:
Item Start Date
A 1/1/01
B 3/15/01
A (blank)
C 2/17/01
D (blank)
A 2/15/01
The result I'm trying to achieve is to show for Item A, 2 processes have started and 1 has not. I've tried every form of Count, CountA and CountIf formulas I can think of and have not found a solution. Any assistance would be appreciated.
Posted by GregC on March 23, 2001 7:17 AM
You can use an array formula with a sum if. Example: All data in column A is Project number and all data in column B is start dates. In column d put project number and in column E put =count(if(a1:a100 = a1,b1:b100,)) When you are done with the formula instead of hitting enter, hit ctl + shift + enter . That should work.
Posted by Aladin Akyurek on March 23, 2001 9:41 AM
Supposing that the projects are in A from A1 on, dates in B from B1.
Enter distinct projects in column C from C2 on.
Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time the following formula:
D2 =SUM((A1:A6=C2)*(B1:B6<>"")) [ or: =SUM((A1:A6=C2)*(not(ISBLANK(B1:B6))) ]
Copy down this as far as needed. The numbers in D from D2 on gives you the number of processes with start date per project.
Array-enter
E2 =SUM((A1:A6=C2)*(ISBLANK(B1:B6)))
Copy down this as far as needed. In E you get the number of processes without a start date per project.
Aladin
Posted by Dave Hawley on March 23, 2001 7:05 PM
Hi Rich
This would be an ideal job for a Pivot Table found under Data on the "Worksheet Menu Bar". If you get stuck let me know and I will send you a simple example.
Dave
OzGrid Business Applications
