MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nested Formula for Management Report

Posted by Rich R on March 23, 2001 7:10 AM


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.


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.


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.

OzGrid Business Applications