Hi All,

I have a scenario where I need to show an overall status for each item (e.g. ID or Name). So the results in column D would show "Not started" for ID1234 as it is the most commonly occurring status and should be logically correct.

However, for ID2345 the logical result should still show "Not Started" as there is one "Not started" across the whole item status.

There is a priority here Not started > In Progress > Completed.

I have tried using the following formula to scan a column and get the least occuring value but dont think it would work logically. Is this something that would be solved with Excel formulas, or do I need to use VBA here?

=INDEX(D2:D8,MATCH(MIN(IF(COUNTIF(D2:D8,D2:D8)=0,"",COUNTIF(D2:D8,D2:D8))),COUNTIF(D2:D8,D2:D8),0))

• excel
• vba
• excel-formula

2 Answers 2

=LET(s,{"Not started","In Progress","Completed"},INDEX(s,MODE.SNGL(SORT(IFERROR(XMATCH([Status],s)/([ID]=[@ID]),"")))))

If I understand it correctly you're not looking for the status that has the highest frequency per ID, but return status Not Started if found, else return In Progress if found, else return Completed (if found).

Using M365 you could use:

=@TOCOL(IFS((B$2:B$7=B2)*(D$2:D$7={"Not started","In Progress","Completed"}),D$2:D$7),2,1)

Or:

=LET(i,B$2:B$7,s,D$2:D$7,L,LAMBDA(x,y,FILTER(s,(i=@i)*(LEFT(s)=x),y)),@L("N",L("I",L("C","")))) (Use in same row as ID you're checking)

Or using older Excel you could use: =INDEX({"Not Started","In Progress","Completed"},MATCH(1,MMULT(TRANSPOSE(N(B$2:B$7=B2)),N(D$2:D$7={"Not Started","In Progress","Completed"})),0))

Not sure the above requires being entered with ctrl+shift+enter