Excel

Copper Contributor

Is there a formula I can use to create an overall status for one user who is tied to multiple applications. 

 

See Excel sheet for example.

12 Replies

In other words, I have thousands of names repeated, each instance of that name has an application tied to it plus a status of whether that application failed or passed. I need to make one status for each person that gives an overall view. If the person is tied to 3 apps that have failed and 3 apps that have passed I would like the overall status to indicate "Not Complete." @jackcherry 

@jackcherry 

Perhaps

=IF(COUNTIFS($B:$B,$B2,$C:$C,"Fail"),"Not Complete", "Complete")

if Not Complete if at least one Fail.

I think this comes close, however, what if the wording is changed slightly. @Sergei Baklan

 

So instead of Pass, Fail, its either Planning, In Progress , or Complete. I am still looking for the same result: if one cell associate with one name is "In Progress" or "Planning" the overall status would be "Not Complete."

 

I am assuming the wording wouldn't matter, but it appears that it does.

@jackcherry 

When

=IF(SUM(COUNTIFS($B:$B,$B2,$C:$C,{"Pass","Complete"}))=COUNTIFS($B:$B,$B2), "Complete","Not Complete")

if ALL are "Complete" OR "Pass" then Complete else Not complete.

 

If only Complete is used you may use only it instead of array constant and remove wrapping SUM

@Sergei Baklan I have been using the formula you provided successfully. However, there are some new variables that have come into play. Instead of two outcomes we now have 3. See example below. 

 

IF(SUM(COUNTIFS($A:$A,$A2,N:N,{"Pass","Wave 1a"}))=COUNTIFS($A:$A,$A2),"Wave 1a Ready","Wave 1b Ready")

 

Workstation J2735AppWaveReadiness
J2735PowerPointWave 1aWave 1a Ready
J2735ExcelWave 1aWave 1a Ready
J2735LDAPWave 1aWave 1a Ready
J2735

PowerBI

Wave 1aWave 1a Ready
T7451OutlookWave 1bWave 2 Ready
T7451LDAPWave 1aWave 2 Ready
T7451SnagitWave 2Wave 2 Ready

U6789

WordWave 1bWave 1b Ready
U6789AccessWave 1aWave 1b Ready

 

The formula does a nice job of giving one or the other, but it is not able to give a third option. 

 

Please let me know if you have any questions.

@jackcherry2375 

Could you please clarify what exactly are these options. I see no one "Pass" in the sample, why for T7451 it's "Wave 2 Ready" and not other one and like.

@Sergei BaklanIn this instance there is no pass of fail. Essentially we would like to know what Wave each workstation will be ready for. For example:

 

If workstations T45126 has 3 apps slotted for Wave 1a and 2 apps slotted for Wave 1b then the overall status of that workstation would be Wave 1b ready. 

 

But if the workstation has 1 apps slotted for Wave 1a and 2 apps slotted for Wave 1b and 1 app slotted for Wave 2 then the overall status of the workstation would be Wave 2. 

 

Essentially if a workstation has just one application that is either Wave 1b or Wave 2 then the overall readiness would should the Wave that is highest. 

@jackcherry2375 I'm assuming that the formula you provided would just need to have an argument added to reflect the change in variables. 

@Sergei Baklan Essentially I need it to understand which workstations are ready for either Wave 1a Wave 1b or Wave 2. 

 

HostnameHigh Level NameWave Package Readiness
TC2675463IBM iSeries Access for WindowsWave 1aWave 1b Ready
TC2675463Microsoft Visual Studio 2010Wave 1aWave 1b Ready
TC2675463Microsoft Visual Studio 2015Wave 1bWave 1b Ready
TC5684390IBM iSeries Access for WindowsWave 1aWave 1b Ready
TC5684390Microsoft Visual Studio 2010Wave 1aWave 1b Ready
TC5684390Microsoft Visual Studio 2015Wave 1bWave 1b Ready
Z789H4593IBM iSeries Access for WindowsWave 1aWave 2 Ready
Z789H4593KeePass Password SafeWave 2Wave 2 Ready
Z789H4593Microsoft Visual Studio 2010Wave 1aWave 2 Ready

 

If there is just one application other than 1a then the overall status of that workstation needs to read the latest wave so in this example the latest Wave would be "Wave 2 Ready". If the workstation has 5 1a and 4 1b then the overall status would be "Wave 1b Ready." 

@jackcherry2375 

Okay, thank you.

That could be

=LOOKUP(2,1/(COUNTIFS(C$2:INDEX(C:C,COUNTA(A:A)),">"&C$2:INDEX(C:C,COUNTA(A:A)),$A$2:INDEX(A:A,COUNTA(A:A)),A2)=0),C$2:C$10) & " Ready"

Please check attached

@Sergei Baklanthe sheet I am working on is quite large. The formula seems to be the solution, but it is not reading Wave 1a in my spreadsheet. I tested a sample by copying and pasting a small portion of my sheet into the spreadsheet you sent me and the formula worked correctly.  

 

Is it possible that the formula isn't able to make all of its calculations in my sheet?

@Sergei Baklan Is there a way to make this formula more efficient? Right now it seems to be having major issues calculating through 14,000 plus lines of data, also casuing the data outputted by the formula to be incorrect. 

 

Excel itself could be the limiting factor here.