Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-693028%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-693028%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20formula%20I%20can%20use%20to%20create%20an%20overall%20status%20for%20one%20user%20who%20is%20tied%20to%20multiple%20applications.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20Excel%20sheet%20for%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-693028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-693079%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-693079%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20other%20words%2C%20I%20have%20thousands%20of%20names%20repeated%2C%20each%20instance%20of%20that%20name%20has%20an%20application%20tied%20to%20it%20plus%20a%20status%20of%20whether%20that%20application%20failed%20or%20passed.%20I%20need%20to%20make%20one%20status%20for%20each%20person%20that%20gives%20an%20overall%20view.%20If%20the%20person%20is%20tied%20to%203%20apps%20that%20have%20failed%20and%203%20apps%20that%20have%20passed%20I%20would%20like%20the%20overall%20status%20to%20indicate%20%22Not%20Complete.%22%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360501%22%20target%3D%22_blank%22%3E%40jackcherry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-693104%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-693104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360501%22%20target%3D%22_blank%22%3E%40jackcherry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3DIF(COUNTIFS(%24B%3A%24B%2C%24B2%2C%24C%3A%24C%2C%22Fail%22)%2C%22Not%20Complete%22%2C%20%22Complete%22)%3C%2FPRE%3E%0A%3CP%3Eif%20Not%20Complete%20if%20at%20least%20one%20Fail.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-693225%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-693225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360501%22%20target%3D%22_blank%22%3E%40jackcherry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUM(COUNTIFS(%24B%3A%24B%2C%24B2%2C%24C%3A%24C%2C%7B%22Pass%22%2C%22Complete%22%7D))%3DCOUNTIFS(%24B%3A%24B%2C%24B2)%2C%20%22Complete%22%2C%22Not%20Complete%22)%3C%2FPRE%3E%0A%3CP%3Eif%20ALL%20are%20%22Complete%22%20OR%20%22Pass%22%20then%20Complete%20else%20Not%20complete.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20only%20Complete%20is%20used%20you%20may%20use%20only%20it%20instead%20of%20array%20constant%20and%20remove%20wrapping%20SUM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-693162%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-693162%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20this%20comes%20close%2C%20however%2C%20what%20if%20the%20wording%20is%20changed%20slightly.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20instead%20of%20Pass%2C%20Fail%2C%20its%20either%20Planning%2C%20In%20Progress%20%2C%20or%20Complete.%20I%20am%20still%20looking%20for%20the%20same%20result%3A%20if%20one%20cell%20associate%20with%20one%20name%20is%20%22In%20Progress%22%20or%20%22Planning%22%20the%20overall%20status%20would%20be%20%22Not%20Complete.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20assuming%20the%20wording%20wouldn't%20matter%2C%20but%20it%20appears%20that%20it%20does.%3C%2FP%3E%3C%2FLINGO-BODY%3E
jackcherry
New 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.

4 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

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies