Capture Text in a Range

[MrDB4Excel]

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.

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)),"")

 

[Fluff]

For col Q you can use

=SUBSTITUTE(TRIM(P3&" "&P4&" "&P5&" "&P6&" "&P7&" "&P8&" "&P9&" "&P10&" "&P11&" "&P12&" "&P13&" "&P14)," ",", ")

Are you happy to change the months in col L into real dates & just display them as Months?

 

[MrDB4Excel]

For col Q you can use

=SUBSTITUTE(TRIM(P3&" "&P4&" "&P5&" "&P6&" "&P7&" "&P8&" "&P9&" "&P10&" "&P11&" "&P12&" "&P13&" "&P14)," ",", ")

Are you happy to change the months in col L into real dates & just display them as Months?

Fluff, thank you so much, this is perfect. I changed the months to the custom date "mmmm", added your formula to a cell, and voila. It only shows January, February, March.
Exactly what I was looking to do.
About the other cells in column N: N6 thru N14 that do not have formulas. Is this at all possible to only get a sum if the month is completed?

 

[Fluff]

You need to Change L1 to an actual date & than use this.
Note I have also change the formula in col P

+Fluff 1.xlsm
	L	M	N	O	P	Q
1	04/04/2022
2	Each Month's Total
3	January	7588	7588		January	January, February, March
4	February	7663	15251		February	January, February, March
5	March	11543	26794		March	January, February, March
6	April	1520
7	May	0
8	June	0
9	July	0
10	August	0
11	September	0
12	October	0
13	November	0
14	December	0
Main

Cell Formulas
Range	Formula
L1	L1	=TODAY()
Q3:Q5	Q3	=SUBSTITUTE(TRIM(P1&" "&P2&" "&P3&" "&P4&" "&P5&" "&P6&" "&P7&" "&P8&" "&P9&" "&P10&" "&P11&" "&P12)," ",", ")
N3:N14	N3	=IF(DATEVALUE("1/"&L3)<EOMONTH($L$1,-1),SUM(M$3:M3),"")
P3:P14	P3	=IF(N3="","",L3)

 

[MrDB4Excel]

You need to Change L1 to an actual date & than use this.
Note I have also change the formula in col P

+Fluff 1.xlsm
	L	M	N	O	P	Q
1	04/04/2022
2	Each Month's Total
3	January	7588	7588		January	January, February, March
4	February	7663	15251		February	January, February, March
5	March	11543	26794		March	January, February, March
6	April	1520
7	May	0
8	June	0
9	July	0
10	August	0
11	September	0
12	October	0
13	November	0
14	December	0
Main

Cell Formulas
Range	Formula
L1	L1	=TODAY()
Q3:Q5	Q3	=SUBSTITUTE(TRIM(P1&" "&P2&" "&P3&" "&P4&" "&P5&" "&P6&" "&P7&" "&P8&" "&P9&" "&P10&" "&P11&" "&P12)," ",", ")
N3:N14	N3	=IF(DATEVALUE("1/"&L3)<EOMONTH($L$1,-1),SUM(M$3:M3),"")
P3:P14	P3	=IF(N3="","",L3)

All good except your formula =IF(DATEVALUE("1/"&L3)<EOMONTH($L$1,-1),SUM(M$3:M3),"") in N3 returns #VALUE!
I left my L1 to be =TEXT(TODAY(),"MMM")
Changed all in P3 to P14 to =IF(N3="","",L3), =IF(N4="","",L4), etcetera thru to P14: =IF(N14="","",L14)

 

[Fluff]

I left my L1 to be =TEXT(TODAY(),"MMM")

You need to change that to a real date. You can change the cell format if desired.

 

[MrDB4Excel]

You need to change that to a real date. You can change the cell format if desired.

Okay, after I did that now my formula in Cell N2: =XLOOKUP(L1,$L$3:$L$23,$M$3:$M$23,0) returns a #N/A
I changed L1 to be =TODAY() and format changed in custom to mmm
Now your formula: =IF(DATEVALUE("1/"&L3)<EOMONTH($L$1,-1),SUM(M$3:M3),"") works fine.
Looking at this formula gives me a headache, meaning I don't understand how it works.
So what causes cell N6 to be blank?

 

[Fluff]

This part DATEVALUE("1/"&L3) converts the month name in L3 into an actual date & if that date is less than the end of last month it runs the Sum otherwise it returns ""

now my formula in Cell N2:

In the data you posted there is no formula in N2

 

[MrDB4Excel]

This part DATEVALUE("1/"&L3) converts the month name in L3 into an actual date & if that date is less than the end of last month it runs the Sum otherwise it returns ""


In the data you posted there is no formula in N2

Here you go, this is the non-bogus file.

2022MonthlySubTotals.xlsm
	H	I	J	K	L	M	N	O	P	Q	R
1	28,314.00			Current Month	Apr	Apr	4/4/2022	14:27:53	Months Previous to Current Month	Mar	26,794.00
2	ÇYear To Date Grand TotalÈ			2022 Annual Totals	Each Month's Total		#N/A		26,794.00
3	Kaufland Total This Month	0.00	1	Kaufland	Jan	7,588.00	7,588.00		Jan	Jan, Feb, Mar
4	Tesco Total This Month	0.00	2	Tesco	Feb	7,663.00	15,251.00		Feb
5	Lidl Total This Month	0.00	3	Lidl	Mar	11,543.00	26,794.00		Mar
6	Penny Market Total This Month	0.00	4	Penny Market	Apr	1,520.00
7	dm Total This Month	0.00	5	dm	May	0.00
8	Obchod Dyleň Total This Month	0.00	6	Obchod Dyleň	Jun	0.00
9	Biller Total This Month	0.00	7	Biller	Jul	0.00
10	SubTotal	0.00	8	Orion	Aug	0.00
11			9	Pepco	Sep	0.00
12	Running Total	0.00			Oct	0.00
13	Non-Product Purchase Total	0.00			Nov	0.00
14					Dec	0.00
December

Cell Formulas
Range	Formula
H1	H1	=SUM(November!H1,December!G1)
L1,N1	L1	=TODAY()
M1	M1	=L1
O1	O1	=NOW()
Q1	Q1	=TEXT(EOMONTH(TODAY(),-1),"mmm")
R1	R1	=MAX(N3:N14)
N2	N2	=XLOOKUP(L1,$L$3:$L$23,$M$3:$M$23,0)
M3	M3	=January!$G$1
N3:N14	N3	=IF(DATEVALUE("1/"&L3)<EOMONTH($L$1,-1),SUM(M$3:M3),"")
M4	M4	=February!$G$1
M5	M5	=March!$G$1
M6	M6	=April!$G$1
M7	M7	=May!$G$1
M8	M8	=June!$G$1
M9	M9	=July!$G$1
M10	M10	=August!$G$1
M11	M11	=September!$G$1
M12	M12	=October!$G$1
M13	M13	=November!$G$1
M14	M14	=December!$G$1
Q3	Q3	=SUBSTITUTE(TRIM(P3&" "&P4&" "&P5&" "&P6&" "&P7&" "&P8&" "&P9&" "&P10&" "&P11&" "&P12&" "&P13&" "&P14)," ",", ")
I3:I9	I3	=SUMIF($B$3:$B$55,K3,$E$3:$E$55)
I10	I10	=SUM(I3:I8)
I12	I12	=G1
I13	I13	=SUM(I12-I10)
P2	P2	=XLOOKUP(Q1,$L$3:$L$23,$N$3:$N$23,0)
P3:P14	P3	=IF(N3="","",L3)

 

[Fluff]

Ok, how about

=XLOOKUP(TEXT(L1,"mmm"),$L$3:$L$23,$M$3:$M$23,0)