0

I'm importing CSV's of all of my transactions in the hopes that I can import a lot of these values into a larger cash flow sheet I've setup elsewhere.

One of the formulas I'm trying to figure out is for Google Sheets to tell me what the balance I have on the first day of each month that is listed on an adjacent drop-down list.

So I have two sets of tables in the following columns:

  1. A,E,F (Date in the format of "MM-DD-YYYY", Transaction Amount, Running Balance): for all of my transactions for the year.

  2. J,K (Month in the format of "MMMM", Balance on the first Day of the month). This list of Months from January-December is in 'J2:J'. The balance on the first day of the adjacent month from 'J2:J' should be listed in 'K2:K'.

For reference, I have the monthly date ranges setup on another tab via column A,B,C -> Month, First Day, Last Day. This was already setup to filter by month on the same sheet, so I'm assuming I could use this to also figure out what the first date of the month is. However, I cannot figure out how to pull the balance from the first day of the month.

Similarly, I'm also interested in figuring out what the sum of all of the transactions that have occurred during a specified month (E2:E) are as well.

Here is the sheet: https://docs.google.com/spreadsheets/d/1aj44YJIrk6el_L0TexQeuLxYapvkXt2b1RbQBwPL13o/edit?usp=sharing

Improve this question
2
  • Linking to a shared spreadsheet is great as supplemental information, but we like posts to be self-contained; a reader shouldn't have to go elsewhere to get the full information. Worse, when the link stops working, the important information here is lost. Please consider including some some data in your question post (and not a screen shot image, either).
    – ale
    Jan 4, 2017 at 19:11
  • Gotcha. Thanks for the heads up. I've edited my initial post per your notes.
    – Tommy
    Jan 4, 2017 at 22:55

1 Answer 1

Reset to default
1

UNTESTED. Assuming the year is always 2015 something like the following in K2 copied down to K13 might suit:

=IF(J2="December",SUMIF(A:A,"<"&DATEVALUE("12/1/2015"),E:E),SUMIF(A:A,"<"&DATEVALUE(ROW()-1&"/1/2015"),E:E))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.