Jun 14 2019 08:55 AM
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
Jun 14 2019 09:28 AM
Perhaps
=IF(COUNTIFS($B:$B,$B2,$C:$C,"Fail"),"Not Complete", "Complete")
if Not Complete if at least one Fail.
Jun 14 2019 10:54 AM
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.
Jun 14 2019 12:48 PM
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
Oct 29 2019 12:28 PM
@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 J2735 | App | Wave | Readiness |
J2735 | PowerPoint | Wave 1a | Wave 1a Ready |
J2735 | Excel | Wave 1a | Wave 1a Ready |
J2735 | LDAP | Wave 1a | Wave 1a Ready |
J2735 | PowerBI | Wave 1a | Wave 1a Ready |
T7451 | Outlook | Wave 1b | Wave 2 Ready |
T7451 | LDAP | Wave 1a | Wave 2 Ready |
T7451 | Snagit | Wave 2 | Wave 2 Ready |
U6789 | Word | Wave 1b | Wave 1b Ready |
U6789 | Access | Wave 1a | Wave 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.
Oct 29 2019 12:53 PM
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.
Oct 29 2019 12:58 PM
@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.
Oct 29 2019 01:00 PM
@jackcherry2375 I'm assuming that the formula you provided would just need to have an argument added to reflect the change in variables.
Oct 29 2019 01:49 PM - edited Oct 29 2019 01:51 PM
@Sergei Baklan Essentially I need it to understand which workstations are ready for either Wave 1a Wave 1b or Wave 2.
Hostname | High Level Name | Wave | Package Readiness |
TC2675463 | IBM iSeries Access for Windows | Wave 1a | Wave 1b Ready |
TC2675463 | Microsoft Visual Studio 2010 | Wave 1a | Wave 1b Ready |
TC2675463 | Microsoft Visual Studio 2015 | Wave 1b | Wave 1b Ready |
TC5684390 | IBM iSeries Access for Windows | Wave 1a | Wave 1b Ready |
TC5684390 | Microsoft Visual Studio 2010 | Wave 1a | Wave 1b Ready |
TC5684390 | Microsoft Visual Studio 2015 | Wave 1b | Wave 1b Ready |
Z789H4593 | IBM iSeries Access for Windows | Wave 1a | Wave 2 Ready |
Z789H4593 | KeePass Password Safe | Wave 2 | Wave 2 Ready |
Z789H4593 | Microsoft Visual Studio 2010 | Wave 1a | Wave 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."
Oct 29 2019 01:54 PM
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
Oct 29 2019 02:51 PM
@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?
Oct 30 2019 09:41 AM
@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.