# 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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### 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
0
Views
223
Replies
0
Views
206
Replies
0
Views
539
Replies
3
Views
790
Replies
7
Views
546

1,186,926
Messages
5,960,595
Members
438,486
Latest member
ncc84330

### 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