I post all my financial transactions in one sheet. In a separate sheet I want to reconcile the credit card charges to the statement by capturing the transaction amounts by statement period. Currently I am capturing the data by month, but want to modify the formula to the date range. Here is what I have:
[Edited by Moderator]
iMac 27″, macOS 15.7
It would be quite easy to have a formula in col J that automates the insertion of statement period.
However, if that is not convenient, then you can consider using SUMIFS, something like this:
=SUMIFS(Transactions::E,Transactions::B,">"&B2,Transactions::B,"<="&C2)
SG
It would be quite easy to have a formula in col J that automates the insertion of statement period.
However, if that is not convenient, then you can consider using SUMIFS, something like this:
=SUMIFS(Transactions::E,Transactions::B,">"&B2,Transactions::B,"<="&C2)
SG
Your current formulas point to column A in the transactions table. That column appears to contain the calendar month.
A simple way to switch from calendar months to statement periods (which overlap calendar months) would be put the statement period in each row of that column A instead of calendar month, e.g. something like "241224-250125" (formatted as text) and have a similar statement period column in each reconciliation table.
SG
CuriousToday wrote:
I don’t think I want to change the Tran Detail sheet Col.
You can't just add a statement period column on the right, say col J, and look at column J instead of B? That's by far the simplest solution, and shouldn't affect formula in your other tables (note the vocabulary distinction between sheets and tables in Numbers).
Formulas looking at date ranges in the existing date column B are possible, but in my experience that approach is more complicated and prone to error.
SG
I don’t think I want to change the Tran Detail sheet Col. A because it is the driver for over a dozen other sheets to produce various monthly financials and analysis. I wanted the formula in the Recon table to look at the Date (Col. B), thereby collecting the transactions in that statement period. I thought there was a formula to capture a transaction by date or date range; is that possible?
SG, I appreciate your efforts to help me. Adding the statement period in Col. J would be really labor intensive. I have multiple credit cards and bank statements and I often post from the receipts rather than waiting for the statements to pile up at the end of the month. Any other suggestions?
Formula for SUMIFS based on date range
