How to calculate Client Repeat Rate? (If any row in a column containing a name corresponds to a particular value then....)

In legacy Excel, this must be entered as an array formula

In Google sheets (assuming the same cell references) then the array aspect must also be made explicit, i.e.

=ARRAYFORMULA(IF(ISNA(MATCH($D2&E$1,$A$2:$A$9&$B$2:$B$9,0)),IF(OR(D2="New",D2="Lost"),"Lost","-"),IF(OR(D2="New",D2="Repeat"),"Repeat","New")))

Filter is Your Friend

On a separate sheet, in cell A1 as a heading lets place the word company and in B1 lets place status.

Now in cell A2 place =UNIQUE('Your Sheet Name'A2:A) to get a unique list of company names. Assuming your company names are on column A and start on the second row.

Before the next part, lets place the year somewhere in the sheet. Lets do in cell D1 and to the left in cell C1 lets type in "Year" for clarification.

Now in cell B2:B1000 put =IF(A2 = "", ,IF(INDEX(SORT(FILTER(Sheet1!$A$2:$B,Sheet1!$A$2:$A = A2),2,FALSE),1,2) >= $D$1,IF(COUNTIF(Sheet1!$A$2:$A,A2) > 1,"Recurring","New"),"Lost"))

We basically are saying If the cell to the left of this formula is not blank and the most recent entry is greater than or equal to the year I placed in D1 then I want to see if this is the first time we have this company in our dataset

If it is, then it would be new development, if not then it would be recurring. And if the most recent entry for this company is not greater than or equal to the year in D1 then it was lost.

grouped deals can be done like:

=INDEX(1*QUERY(A3:C, "select count(A) where A is not null group by A pivot C"))

and status like:

=ARRAYFORMULA(QUERY(IF(ISNUMBER(
 QUERY(A3:C, "select count(A) where A is not null group by A pivot C"))*1=0, "lost", IF(ISNUMBER(
 QUERY(A3:C, "select count(A) where A is not null group by A pivot C"))*1>ISNUMBER(
 QUERY(A3:C, "select A,count(A) where A is not null group by A pivot C"))*1, "new", "repeat")), "offset 1", ))