# determine if a customer has not paid in x months

#### nelsok

##### Board Regular
i have a spreadsheet with customers in column a and the associated monthly revenue from each customer in the next 25 columns.

I want to be determine if there are 0's in the x most recent months.
where x is any number.

so if i want to know if we recieved no revenue from a customer in the last 1 months it would be

assume b2:z2 are monthly dates 1/1/2007, 2/1/2007, etc...
=if(hlookup(match(now(),b2:z2,1),b2:z2,2,0)<=0,"not an active customer","active customer")

i just dont know how to do this for more than 1 month.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### tusharm

##### MrExcel MVP
Suppose you have the number of months of interest in E5. Then, use the formula
=COUNTIF(OFFSET(B2,1,MATCH(NOW(),B2:N2,1)-(E5),1,E5),0)=E5
where B2:N2 represents the first of each month and the payments are in row 3.

#### nelsok

##### Board Regular
thanks... if i am understanding the formula correctly, the formula returns true if the line does not have revenue within the number of months in e5.

Replies
2
Views
255
Replies
1
Views
107
Replies
1
Views
240
Replies
1
Views
202
Replies
3
Views
186

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,102
Messages
5,768,104
Members
425,454
Latest member
khoro

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back