I have a bit of an issue and thought this might be a good place to get some ideas. I have a set of data with client ID going vertically down and then horizontally I have the date the client moved to a certain status. I want to know at any given time how many clients are in each status. I also have other items here, but for now that's my only goal and ultimately I want to graph it.

Now I couldn't come up with a way to write a single formula to calculate the count for each day, perhaps there's a way to do that?

Client ID | Application | Document Gathering | Approving | Complete | Withdrawn | Declined

1 | 12/31/2010 | 1/5/2011 | | | | 1/15/2011

2 | 1/15/2011 | 1/23/2011 | 1/23/2011 | 1/25/2011 | |

Above is a simple example of how the data looks.

Now I did come up with a way to do a test for each loan to see what day they are in a give status:

=vlookup(

MAX(

IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,9,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,9,0))<=CFP$1,1,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,10,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,10,0))<=CFP$1,2,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,11,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,11,0))<=CFP$1,3,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,12,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,12,0))<=CFP$1,4,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,13,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,13,0))<=CFP$1,5,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,14,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,14,0))<=CFP$1,6,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,15,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,15,0))<=CFP$1,7,0)

,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,16,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,16,0))<=CFP$1,8,0)

)

,Status,2,0)

This works and I just end up with "end status" growing but I am not concerned with the end cases, just the cases leading up to complete, declined, or withdrawn. The logic is that I have a numerical value for each status, calculated the max value which is the current status and then reference a "status table" to define the numerical values.

However this is ultimately make creating a table to do then do a quick countifs and get my results vs. being able to do this in one place. Is there optimally a way to write a formula that will calculate the status based on the table logic above or a way to write that mess of vlookups (I have a feeling sumifs is a better choice than vlookup but I'm not sure it's that much faster) in a form that is not going to overwhelm my computer?

Is there a way to can either make the above much simplier