Hello there!
I have a workbook with 4 spreadsheets; only the first 2 are relevant right now. The first one is called "MONTH END TOTALS" and the second is called "COMPLETE2".
- COMPLETE2 has a LOT of rows (thousands), with column A being dates. The number of rows on this sheet will vary and there might (rarely) be blank rows scattered in there.
- MONTH END TOTALS has a series of tables to summarize the data from COMPLETE2. The top row of each table lists the months of the year and just above that is the year.
My first problem was finding out where the last row of data is on COMPLETE2 just using a formula. (Initially, my coworker asked for a macro that would carry out the work on this workbook, but I thought that if I could get formulas to do what's needed, the sheet would always be up-to-date.) I planned to incorporate that formula into the others that will populate the rest of the table on MONTH END TOTALS, but it was going to make for some RIDICULOUSLY long formulas. This is where the question in my subject is aimed: So, I put a formula in O20 on MONTH END TOTALS that will keep track of what row is the last one on COMPLETE2 with data. Then I thought it'd be easy to use O20 in the other formulas as part of the range reference. (For example - A2:A (O2)). I was WRONG!! Very, VERY wrong! I've tried innumerable formulas and syntaxes and I just can't get anything to work. Two of my attempts are shown on MONTH END TOTALS, cells C3 and K3.
In the first data row of the first table I need to have a formula that tells me how many rows on COMPLETE2 have a date where the month/year corresponds to the month/year for that cell in the table.
So, on the attached workbook, MONTH END TOTALS cell C4 should show how many rows on COMPLETE2 are for AUG 2017, for example. That would be 7 rows. Cell K4 should show how many rows on COMPLETE2 are for APR 2017.
I hope I have explained my problem(s) at least a little clearly; I really tried to make sense. Please let me know if I've been totally confusing. I've been wrestling with this for 2 solid days now and my brain is DEAD!
Thanks for any help!
Jenny
Bookmarks