Need Help?? (Not sure what function to use)

[Jarodjp42]

Hi, I am trying to write a formula that will tell me if a client is new or existing. Below is some sample data of the file I am working with.

Client	New/Existing	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19
Jeff	Existing	$ 227,828	$ 300,773	$ 401,894	$ 301,140	$ 541,519	$ 306,717	$ 233,761	$ 334,775	$ 267,705	$ 247,118	$ 327,502	$ 301,082	$ 268,381	$ 249,657	$ 298,969	$ 233,347	$ 331,162	$ 337,342	$ 226,385	$ 256,811	$ 277,774	$ 234,745	$ 270,612	$ 257,375	$ 220,512
Brian	Existing	$ 1,317	$ 4,339	$ 2,003	$ 6,548	$ 4,669	$ 159	$ -	$ 2,519	$ 7,246	$ 2,568	$ 12,412	$ 4,029	$ -	$ 2,324	$ 3,915	$ 3,101	$ 4,718	$ 5,561	$ 3,011	$ 4,979	$ 4,557	$ 7,150	$ 14,316	$ 7,165	$ 2,764
Jordan	Existing	$ 179	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 78	$ -	$ 100	$ -	$ -	$ -	$ -	$ -	$ 1,627	$ 1,019	$ 115	$ 101	$ 1,951	$ -
Michael	New	$ 500	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 650	$ -	$ -	$ -	$ 26	$ 1,466	$ -	$ -	$ -	$ -	$ -	$ -
Mike	Existing	$ -	$ -	$ -	$ -	$ 851	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 396	$ -	$ -	$ -	$ 1,314	$ -	$ 99	$ -	$ -	$ 1,977	$ 629	$ -	$ -
Jack	New	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 477	$ 134	$ -	$ -	$ -	$ 976	$ -
Allison	New	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 595	$ -	$ -	$ 1,152	$ -	$ -	$ -	$ -
Danielle	New	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 1,922	$ -	$ -	$ -	$ -	$ -	$ -
Ashley	Existing	$ (1,704)	$ 1,832	$ 12,472	$ -	$ 9,638	$ 735	$ -	$ -	$ -	$ -	$ -	$ 1,013	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Chris	New	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Sarah	New	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 3,397	$ -	$ 63	$ -

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

I am trying to write a formula in column B that will tell me if a client is new or existing. My rules for being a new client is they do not have any revenue for 12 consecutive months. To explain further, we received revenue from Michael in Feb 2017 but then didn't receive any revenue for the next 12 months after that. Because of that Michael's status has now gone from existing to new.

So I am looking for a formula that will look at when a client received revenue and then count the next 12 columns over. If the total adds up to 0 then the clients status should say new. If a client receives revenue 13 months after the last time I received revenue then I want it to start counting from that value. If you can help me write a formula that will do this I would be much appreciated. Thank you tons in advance!!!

 

[DanteAmor]

Try this formula

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:66px;" /><col style="width:99px;" /><col style="width:64px;" /><col style="width:61px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:58px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:58px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:61px;" /><col style="width:64px;" /><col style="width:61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Client</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">New/Existing</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-17</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">mar-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Apr-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">may-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">jun-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">jul-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aug-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">sep-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">oct-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">nov-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Dec-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Jan-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-19</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Jeff</td><td >Existing</td><td style="text-align:right; ">227,828</td><td style="text-align:right; ">268,381</td><td style="text-align:right; ">249,657</td><td style="text-align:right; ">298,969</td><td style="text-align:right; ">233,347</td><td style="text-align:right; ">331,162</td><td style="text-align:right; ">337,342</td><td style="text-align:right; ">226,385</td><td style="text-align:right; ">256,811</td><td style="text-align:right; ">277,774</td><td style="text-align:right; ">234,745</td><td style="text-align:right; ">270,612</td><td style="text-align:right; ">257,375</td><td style="text-align:right; ">220,512</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Brian</td><td >Existing</td><td style="text-align:right; ">1,317</td><td > </td><td style="text-align:right; ">2,324</td><td style="text-align:right; ">3,915</td><td style="text-align:right; ">3,101</td><td style="text-align:right; ">4,718</td><td style="text-align:right; ">5,561</td><td style="text-align:right; ">3,011</td><td style="text-align:right; ">4,979</td><td style="text-align:right; ">4,557</td><td style="text-align:right; ">7,150</td><td style="text-align:right; ">14,316</td><td style="text-align:right; ">7,165</td><td style="text-align:right; ">2,764</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Jordan</td><td >Existing</td><td style="text-align:right; ">179</td><td > </td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">1,627</td><td style="text-align:right; ">1,019</td><td style="text-align:right; ">115</td><td style="text-align:right; ">101</td><td style="text-align:right; ">1,951</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Michael</td><td >New</td><td style="text-align:right; ">500</td><td > </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Mike</td><td >New</td><td > </td><td style="text-align:right; ">396</td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Jack</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">477</td><td style="text-align:right; ">134</td><td > </td><td > </td><td > </td><td style="text-align:right; ">976</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Allison</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">595</td><td > </td><td > </td><td style="text-align:right; ">1,152</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Danielle</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">1,922</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Ashley</td><td >New</td><td style="text-align:right; ">-1,704</td><td > </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; text-align:right; ">-25</td><td style="background-color:#ccff33; text-align:right; ">25</td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Chris</td><td >New</td><td > </td><td > </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Sarah</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">3,397</td><td > </td><td style="text-align:right; ">63</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(SUM(OFFSET(C2,0,MATCH(MAX($1:$1),$1:$1,0)-14,,12))=0,"New","Existing")</td></tr></table></td></tr></table> <br /><br />

 

[Jarodjp42]

@DanteAmor Thank you for your response. However, the formula isn't doing exactly what I am looking for. Sorry for the poor explanation. Hopefully, I can explain it a little better this time.

I have a total of 25 trailing months from the end of the month today. I am looking for a formula that will look at the first month (Feb-17) and if there is revenue move to the next column and so on until it finds a month with no revenue. Once it finds a month with no revenue I want it to sum that column and the next 11, so a total of 12 months. If the total of the 12 months equals 0 then I want the status of the client to be "New". I am also looking for a formula (hopefully we can incorporate this into the same formula) that will look for the first amount of revenue and if it was in 2019 then return the status of "New". Another problem I am trying to figure out is that with this formula summing up the next 12 months after a month with revenue, how do I get the status to stay existing if it sums up the next couple of months after a month with revenue but there are no more months with data. I still want the status to stay existing even tho when you sum up those months it is going to equal 0. Hopefully, you can help me with my current situation. I appreciate the help you can provide. Thank you!

PS if you need more explanation please feel free to reach back out to me! Thanks again!

 

[DanteAmor]

It would be very helpful if you explain with examples each of the cases that can be presented. In this way we can prepare a formula that covers all cases.

 

[Jarodjp42]

@DanteAmor Hopefully this helps. Thanks again for your time and help!

My criteria:

- If the first month of revenue received comes in 2019 status should be "New"
- If we don't receive any revenue for 12 consecutive months status should be "New"
- If we receive revenue after 12 months of no revenue for a client the following year should have the status of "1st full year"
- Otherwise, the client's status should be "Existing" if we have revenue for the client.

Client	New/Existing	17-Feb	17-Mar	17-Apr	17-May	17-Jun	17-Jul	17-Aug	17-Sep	17-Oct	17-Nov	17-Dec	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	19-Jan	19-Feb
Michael	New	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	100	300

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we received the first month of revenue in 2019 so status is "New"

Client	New/Existing	17-Feb	17-Mar	17-Apr	17-May	17-Jun	17-Jul	17-Aug	17-Sep	17-Oct	17-Nov	17-Dec	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	19-Jan	19-Feb
John	New	200	0	0	0	300	200	150	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	120	0	0

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we received revenue in June & July of 2017 but then didn't receive revenue for the next 12 months so that should change the clients status to "New".

Client	New/Existing	17-Feb	17-Mar	17-Apr	17-May	17-Jun	17-Jul	17-Aug	17-Sep	17-Oct	17-Nov	17-Dec	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	19-Jan	19-Feb
James	New	200	350	0	0	0	0	0	0	0	0	0	0	0	0	0	300	0	0	0	0	0	200	120	0	0

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we received revenue from James in Feb & Mar 2017 but then didn't receive any revenue for 12 consecutive months. Once we received revenue again the clients status should be "New" until Jan 2019 and at that point, it should change from "New" to "1st Full Year"

Client	New/Existing	17-Feb	17-Mar	17-Apr	17-May	17-Jun	17-Jul	17-Aug	17-Sep	17-Oct	17-Nov	17-Dec	18-Jan	18-Feb	18-Mar	18-Apr	18-May	18-Jun	18-Jul	18-Aug	18-Sep	18-Oct	18-Nov	18-Dec	19-Jan	19-Feb
Jake	Existing	$1,317	$4,339	$2,003	$6,548	$4,669	$159	$ -	$2,519	$7,246	$2,568	$12,412	$4,029	$ -	$2,324	$3,915	$3,101	$4,718	$5,561	$0	$0	$0	$0	$0	$0	$0

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we have received revenue from jake on a regular basis which means he should have a status of "Existing". However, I still want to have the "Existing" status for Jake even though we haven't received any revenue for the last 7 months.

I am hoping to get a formula that can look column by column until it finds revenue and then calculates out 12 months going forward to see if there is revenue or not. If no revenue for 12 consecutive months status is "New"

I hope this makes it a little more clear. If not please let me know and I can provide more detail. Thanks!

 

[DanteAmor]

For that you need 2 auxiliary columns and the following 3 array formulas.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:66px;" /><col style="width:99px;" /><col style="width:99px;" /><col style="width:99px;" /><col style="width:64px;" /><col style="width:66px;" /><col style="width:69px;" /><col style="width:63px;" /><col style="width:68px;" /><col style="width:64px;" /><col style="width:69px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td><td >AE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Client</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">New/Existing</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aux 1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aux 2</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-17</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">oct-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">nov-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">dic-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ene-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">mar-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">abr-19</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Jeff</td><td >New</td><td style="text-align:right; ">29</td><td style="text-align:right; ">24</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">80</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Brian</td><td >New</td><td style="text-align:right; ">9</td><td style="text-align:right; ">12</td><td > </td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Michael</td><td >Existing</td><td style="text-align:right; ">5</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">7</td><td > </td><td style="text-align:right; ">8</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Jordan</td><td >1st Full Year</td><td style="text-align:right; ">9</td><td style="text-align:right; ">16</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >{=IF(MATCH(YEAR(TODAY()),YEAR($1:$1),0)<=C2,"New",IF(D2>=12,IF(SUM(OFFSET(A2,0,MATCH(2019,YEAR($1:$1),0)-1,,MONTH(TODAY())))>0,"1st Full Year","New"),"Existing"))}</td></tr><tr><td >C2</td><td >{=MIN(IF(OFFSET(E2,0,0,,MATCH(MAX($1:$1),$1:$1,0))<>0,COLUMN(OFFSET(E2,0,0,,MATCH(MAX($1:$1),$1:$1,0)))))}</td></tr><tr><td >D2</td><td >{=MAX(FREQUENCY(IF(E2:AE2=0,COLUMN(E2:AE2)),IF(E2:AE2<>0,COLUMN(E2:AE2))))}</td></tr></table></td></tr></table> <br /><br />


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Jarodjp42]

@DanteAmor Thank you so much. This is exactly what I was looking for. Can you explain to me what columns C & D are doing? So I have a better understanding of the formula. Once again thank you for all your help!

 

[DanteAmor]

@DanteAmor Thank you so much. This is exactly what I was looking for. Can you explain to me what columns C & D are doing? So I have a better understanding of the formula. Once again thank you for all your help!

I'm glad to help you. Thanks for the feedback.


Column C is to know when they made the first payment.
Column D is to count how many consecutive cells have 0

 

[Jarodjp42]

@DanteAmor after incorporating the formulas you provided me to get my desired outcome. I came across one instance that I am not sure how to fix. I was hoping you could help if possible. Here is my issue with the current formula you provided me.

{=MAX(FREQUENCY(IF(E2:AE2=0,COLUMN(E2:AE2)),IF(E2:AE2<>0,COLUMN(E2:AE2))))}

The specific issue I am running into is when a client, max, for example (shown below), doesn't have any revenue for the first 12 months (Mar 2017 - Feb 2018). Then we receive revenue on the 13th month, (Mar 2018) and then go another 11 months without revenue (Apr 2018 - Feb 2019) and then received revenue on the 12th month after Mar 2018, (Mar 2019). The status for Max should be Existing but the formula has it as "New" because it is pulling the 12 consecutive months without revenue. What I actually want the formula to do is pull the 11 consecutive months without revenue. The formula is using the max function to find the max amount of consecutive months without revenue after it calculates the first part of the formula. So for this specific example, the formula would read {=Max(12,11,0)}. So instead of it pulling the max number of consecutive months without revenue (12), I am looking for something that pulls the later value of consecutive months (11) and changing the status of the client to "Existing". Do you know of a function or formula that would do the same thing that you already helped me with but instead pull the later consecutive months without revenue instead of the max amount of consecutive months without revenue? Thank you in advance for any help or direction to can provide. Also, if I have explained it well enough or you need more information please let me know! Thanks again.

	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19
Max	0	0	0	0	0	0	0	0	0	0	0	0	600	0	0	0	0	0	0	0	0	0	0	0	400

<colgroup><col width="64" span="26" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Jarodjp42]

@DanteAmore Here is another example I wanted to provide you to maybe better clarify for you so you know exactly what I am trying to accomplish.

Below I have a client, Adam. In column D (Aux 2) I get 12 when I use the following formula {=MAX(FREQUENCY(IF(E2:AE2=0,COLUMN(E2:AE2)),IF(E2:AE2<>0,COLUMN(E2:AE2))))} however what I am trying to get is 7 instead of 12. The 12 is getting returned because of the max function in the formula. I am not sure how to return the 7. Basically, I am looking for the last set of consecutive months without revenue after the last month that revenue was received. So is there another function I can replace MAX with to look at the data and find the last column with revenue and then count the consecutive months with no revenue? Thanks!

Client	status	Aux 1	Aux 2	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19
Adam	Existing	13	12	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 860	$ 12,678	$ -	$ 955	$ 29,656	$ 6,105	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Client	status	Aux 1	Aux 2	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19
Adam	Existing	13	7	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 860	$ 12,678	$ -	$ 955	$ 29,656	$ 6,105	$ -	$ -	$ -	$ -	$ -	$ -	$ -

<colgroup><col width="64" span="29" style="width:48pt"> </colgroup><tbody>
</tbody>