Hello! National Doughnut Day just passed and being wired from the sugar on a warm Friday night, I decided to tackle an annoying problem: determining which transactions on my Citi AT&T Access More statement earned 3X bonus points at retail and travel websites (yes, nerdy thing to do). While I’m only concerned about the AT&T card in today’s post, this frustration also applies to other Citi cards that earn Thank You Points, such as Citi Prestige, Premier, Forward, etc. Knowing what counts or doesn’t count for bonus points will help all of us build a valuable database of transactions that are eligible for this generous card, such as the one that Miles to Memories started, and this one from Doctor of Credit.

However, unlike Chase and Barclay, who make it super easy to see exactly how many points each transaction earned, Citi just shows you a total for each bonus category. This is very annoying if you have a lot of transactions (and you should be experimenting a lot!) because you basically have trial-and-error to figure out which ones earned 3X and which ones didn’t. After a few minutes of writing Excel functions and randomly trying to set some Paypal transactions to 3X and others to 1X and not successfully getting the 3X to add up to what Citi claimed I earned, I knew there had to be an easier way to do this quickly. I’m lazy and like to save time. 😛

I googled “use excel to determine which cells add up to a certain number” and found that Microsoft Excel’s Solver function does exactly what I am trying to do. The rest of this post is a tutorial to show you how to use it for our specific purpose. Don’t be overwhelmed, it’s actually *really* easy! I will include lots of pictures for you to follow along the way, and after you do it once, you’ll be able to do it on your own and save lots of time. 🙂

###### Activate Solver

You’ll only need to do this once. Go to the menu Tools, and click Excel Add-ins. Check the Solver Add-In box and click OK.

###### Download The Data From Citi

- Sign into your account
- Scroll to your card and click “Download Statements (PDF)”
- Select your desired statement date, and download two files — the PDF (Adobe) and CSV (Excel) files.
- Open up the statement PDF and scroll to Page 2 where your Thank You Points earned are listed. Write down the number listed next to “Other Purchases”. This is your 1X category; the purchases that did NOT earn the 3X.

###### Set Up the Excel File

- Open up the statement CSV file in Excel.
- Delete the rows that indicate payments to your account (leave your returns though).
- Insert a column to the right of the transaction amounts and type the number 1 and fill down the column.
- A few cells down from the data in Column C, type in the following formula to multiply all the values from Column B with Column C and add them up (substitute the cell numbers to capture all the data in your Columns B & C):
=SUMPRODUCT(B1:B5,C1:C5)

###### Run Solver

- Open the Solver add-in by going to the menu Tools > Solver, or by going to the Data tab on the ribbon and clicking Solver on the far right.
- Fill out the Solver Parameters window as follows:
- In “Set Objective,” enter the cell number where you had put the formula. In my case above, it’s $C$9.
- Set “Value of” to your 1X value from your statement. In my case, it was 220 Thank You Points.
- Set “By Changing Variable Cells” to the columns you typed all those 1’s in. Instead of typing, you can click in the field, drag your mouse on the cells, and it’ll fill in the correct range for you.
- Click “Add” and set a constraint for that same column to “Bin”, and click Ok. This will add a Constraint where your column of 1’s must remain binary numbers (1’s or 0’s).
- You can leave “Make Unconstrained Variables Non-Negative” checked, though it is not relevant to our case.
- Select Solving Method “Simplex LP”. Click on Options and uncheck “Ignore Integer Constraints”.

- Click Solve! Hopefully, it will tell you that they found a solution. If so, congratulations! Click OK.

###### Interpreting The Results

This is what your spreadsheet should look like now. You’ll notice that the Solver tool changed some of your 1’s into 0’s. The line items that got changed to 0’s are the transactions that earned 3X TYP, and the ones that remained as 1’s are the unbonused transactions (sad!). Ideally, you’d like to see more 0’s than 1’s!

If you’re a more visual person and would like to enhance this view, you can also apply Conditional Formatting to highlight the 0’s (or 1’s).

So there you have it! How to quickly and easily figure out which transactions earned 3X and which didn’t. The example I gave above was my first month of using the card, so I didn’t have many transactions, and most of them earned 3X, so I was lucky. I wouldn’t have needed the Solver tool since I could very quickly and easily see that the 220 unbonused points I earned matched up exactly with one specific transaction. However, my statement after this had maybe 50+ transactions from maybe 20 different vendors, so using Solver helped me figure out which vendors coded as 3X. Hope this helps!

P.S. Founderscard coded as 3X TYP! 😀

P.P.S. CAVEAT: I didn’t address the possibility that there might be different combinations of your transactions that would all add up to the target amount. For example, if you got 90 TYP in the 1X and 30 TYP in the 3X and you had 10 transactions from different vendors that each charged exactly $10. Which vendor gave you the 3X?? I think these scenarios will probably be fairly rare though. Does anyone know if we can call Citi customer service to find out which of those 10 vendors had the 3X?

## Leave a Reply

12 Comments on "Using Excel’s Solver Function to determine which Citi transactions earned bonus points"

This is awesome! Great post!!

Thank you!! Glad it helps, and hopefully I can get some suggestions to improve the results 😀

Thanks for the great idea! But unfortunately I have tried a few statements and not been able to get it to solve. Possibly because of rounding (partial dollar) issues? Possibly because of how Citi handles (or mishandles?) returns and points. Have you (or anyone else) been able to check multiple statements and verify that this works? If so, perhaps I’m doing something wrong. But I followed your instructions and I clearly understand what the spreadsheet and solver solution you’ve come up with is trying to accomplish.

Sorry for the ridiculously delayed response…looks like I didn’t get email alerts for these >_< But I hope you figured it out! I would have probably suggested trying to round the numbers manually since you can modify any of the values. I'm not sure if that'll help…it could also be that Citi messed up. 🙂

THANK YOU!!! This is fantastic. I don’t use Excel but the instructions worked for the Solver add-in for Google Docs as well.

Woohoo!! Thanks for reporting back that it works for Google Sheets!! 😀

Cool…was just reading about this tool in connection with linear programming problems.

I would add more constraints though, as something as simple as a $120 + $100 transactions elsewhere could throw this off and give two possible solutions.

The 0/1 should probably get multiplied by the 2x bonus points and total sum of those must match total points awarded.

Even then in theory a $100 at 1x can’t be separated from $33.33 at 3x.

Yeah, you’re right, it’s really hard to tell with the multiple solutions, in those cases we might just need to ask Citi whether X or Y transaction counted. :\

Update on this: so my cobloggers suggested manually grouping together transactions from the same merchant, i.e. pooling all the Amazon transactions together into one cell, etc. Presumably, all transactions from the same merchant either earn 3X or 1X (this is actually not always the case, but we can assume it is for simplicity), so grouping it together and then running Solver should lead to slightly more accurate results.

Hi,

There is a easy way to see what transactions eared 3x online!

In your Citi At&t account, press “Points summary”

Select your At&t Card and press “Continue”.

You will see the amount of points you earned over the months you choose. Changing from “view by account” to “View as a List” will give you a list of transaction dates and a icon showing “3x” or “1x”.

You cannot see the merchant, but you can see the date and amount of points earned, so its pretty easy to put 2 and 2 together.

Gallery of screenshots: https://sli.mg/a/B4hQzr

I’m sincerely sorry that you have to go through all this trouble to figure out solver.

Thanks for your comment, Zal!! Omg, don’t be sorry at all! It was no trouble at all, took a few minutes, and it was really cool to see it work in the process. I spent more time ASKING people how to figure out 3x vs 1x and nobody knew, so I’m glad you finally spoke up.

UPDATE: Hey Zal, I went through your instructions and even double checked against your screenshots. Unfortunately, your method does not reveal individual transactions and which transactions received 3X vs. 1X. It just shows everything from one statement lumped together – i.e. all the 3X from one month’s statement is lumped together, and all the 1X is lumped together. It is the same information that the statement gives, as seen in this post. Thanks for your help though — if you ever figure out an even better way to figure out individual transactions’ earnings, let us know!! 🙂

[…] used an Excel Solver formula thanks to the writers at ‘Dem Flyers to check the earning on my student loan […]