# IF Statement - Payment Collection Spreadsheet - Cash Conversion to Non-Cash Payment Tracking

#### steve5740

##### New Member
Hello fellow members,

I appreciate you taking the time to view this post, and hope that I can fully explain the challenge that I need assistance with.

I am working on a project that we are working with our customers to no longer pay with cash, and have given the option to pay with check, credit card or eCheck.

My spreadsheet columns include Account Number(A), Account Name (B), Payment Date (C), Payment Type (D, this refers to Cash, Check, Credit Card, eCheck), Receipt Number (E, Check # if check), Payment Amount (F). Columns G-J include Cash, Check, Credit Card, eCheck. I have written an IF statement in these columns to count the times when the customer has paid by cash, check, credit card or eCheck. Next, I performed a Subtotal for all payment types.

My challenge is that I need to create an IF statement that will identify any customer that has paid with non-cash (check, credit card, eCheck) on the last 3 consecutive deliveries.

What is the best function to achieve the desired result? Thanks again.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi Steve,
Here is an idea, I tried to do a formula where rows can be unsorted but I could not, so if you have a set like this

You sort the data by customer and date
Then you add to D2 the formula =IF(A2=A1,IF(AND(A2=A1,C2="Nocash"),1+D1,0),IF(C2="Nocash",1,0))
And in E2 the formula =IF(D2>2,"Yes","No")

This is the data I used:
Code:
``````customer date      pay by
a        7/2/2016  Nocash
a        7/17/2016 Nocash
a        7/21/2016 Cash
a        7/26/2016 Cash
a        8/17/2016 Nocash
a        9/25/2016 Nocash
a        9/28/2016 Nocash
b        8/10/2016 Cash
b        8/15/2016 Cash
b        8/17/2016 Nocash
b        8/20/2016 Cash
b        9/6/2016  Cash
b        9/12/2016 Nocash
b        9/13/2016 Cash
c        7/3/2016  Nocash
c        7/7/2016  Nocash
c        7/28/2016 Nocash
c        9/28/2016 Cash``````

Column D has the amount of consecutive Nocash delivers
Column E has if the amount is 3 or more consecutive Nocash delivers
I hope this helps
Cheers
Sergio

Sergio,

This is very helpful, and has given me an idea on how to move forward with this. I may reach out to you again for further discussion.

Thank you again.

Steve Lopez

Hi Sergio,

How could I incorporate a statement into the existing statement that would identify a customer that has paid with noncash 3 times in a row but then paid cash 2 times in a row? I would like for the end result to say "Review" in Column E in your chart above.

Thank you again.

Hi Steve
You can use this in E2: =IF(D2>2,"Yes",IF(AND(D1>2,C2="Cash",A2=A1),"No.",IF(AND(C2="Cash",A2=A1,E1="No."),"Review","No")))
Cheers
Sergio

Last edited:
Hi Steve
You can use this in E2: =IF(D2>2,"Yes",IF(AND(D1>2,C2="Cash",A2=A1),"No.",IF(AND(C2="Cash",A2=A1,E1="No."),"Review","No")))
Cheers
Sergio

Hola Sergio,

Thank you again for your assistance. The above statement works however is there a way that it can be written to only say "Review" once after a customer has returned to Cash 2 times in a row.

For example: Customer A paid 3 times in a row with NonCash and Column E now says "Yes". Customer A then pays Cash 6 times in a row therefore every other row says "Review". Is there a way to only say "Review" at the second of six cash payments? I hope this makes sense.

Thank you,

Steve

Last edited:
Hello Sergio,

The sample seems to give me the desired result.

Thank you!

Steve

Replies
0
Views
314
Replies
0
Views
288
Replies
14
Views
745
Replies
3
Views
439
Replies
13
Views
776

1,196,307
Messages
6,014,570
Members
441,828
Latest member
cofracr

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