MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
I have searched through dozens of web pages for answers to the following but to no avail.
In this Excel sheet, you see a list of months in column L with corresponding values in column M.
Next, in column N are running totals but because April is not finished then cell N6 is blank (does not yet have the formula to add M6 to N5).
Formulas are not yet inserted in N6 through N14 because elsewhere I am getting the value in column N that represents the total from the previous full months prior to the current month of April in this scenario.
So what formula can be inserted in column N that would only total all previous full months that have expired?
In addition, I also need to get a comma delineated list of the months that appear in column P. You can see I have formulas in P3 through P14.
In column Q you can see my attempts at getting this list of months prior to the current month but they contain commas that represent the empty cells. I do not want these commas to show.
Perhaps I am not creating a good working formula in the first place.
I would so appreciate some advice on how to solve this.
In this Excel sheet, you see a list of months in column L with corresponding values in column M.
Next, in column N are running totals but because April is not finished then cell N6 is blank (does not yet have the formula to add M6 to N5).
Formulas are not yet inserted in N6 through N14 because elsewhere I am getting the value in column N that represents the total from the previous full months prior to the current month of April in this scenario.
So what formula can be inserted in column N that would only total all previous full months that have expired?
In addition, I also need to get a comma delineated list of the months that appear in column P. You can see I have formulas in P3 through P14.
In column Q you can see my attempts at getting this list of months prior to the current month but they contain commas that represent the empty cells. I do not want these commas to show.
Perhaps I am not creating a good working formula in the first place.
I would so appreciate some advice on how to solve this.
| Bogus RangeCapture.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| L | M | N | O | P | Q | |||
| 1 | April | |||||||
| 2 | Each Month's Total | |||||||
| 3 | January | 7,588.00 | 7,588.00 | January | January, February, March, , , , , , , , , , | |||
| 4 | February | 7,663.00 | 15,251.00 | February | JanuaryFebruaryMarch | |||
| 5 | March | 11,543.00 | 26,794.00 | March | January, February, March, , , , , , , , , | |||
| 6 | April | 1,520.00 | ||||||
| 7 | May | 0.00 | ||||||
| 8 | June | 0.00 | ||||||
| 9 | July | 0.00 | ||||||
| 10 | August | 0.00 | ||||||
| 11 | September | 0.00 | ||||||
| 12 | October | 0.00 | ||||||
| 13 | November | 0.00 | ||||||
| 14 | December | 0.00 | ||||||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L1 | L1 | =TEXT(TODAY(),"MMMM") |
| N3 | N3 | =M3 |
| N4:N5 | N4 | =SUM(N3+M4) |
| Q3 | Q3 | =CONCATENATE("January, ","February, ","March, ",", ",", ",", ",", ",", ",", ",", ",", ",", ") |
| Q4 | Q4 | =CONCATENATE(P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14) |
| Q5 | Q5 | =P3&", "&P4&", "&P5&", "&P6&", "&P7&", "&P8&", "&P9&", "&P10&", "&P11&", "&P12&", "&P13&", "&P14 |
| P3:P14 | P3 | =IFERROR(INDEX(L3:L14,MATCH(N3,N3:N14,0)),"") |
