Multiple sums query, is it possible?

[dispelthemyth]

I am trying to sum 3 periods of time,
1) This month only
2) This month and last month
3) This month and the last 2 months

As can be seen in June, i am trying to get an array of 30, 61 and 91 so i can use for a comparison

I have tried many formulas but nothing seems to work; an example For March, in D4 I have used

= SUM(OFFSET(B4, -{2;1;0}, 0, 3, 1))

(Used the sum so it doesnt spill for now, but i get 90, if i let it spill i get 3 #Value errors
Is it possible to get an array of sums with 3 cells, 2 cells and 1 cell?

EDIT:
I have also updated the formula to change the height to 3 rows, 2 rows and 1 rows as i mistakenly left it at 3 but it still gives a #value error with no sum wrapper and 90 with the sum wrapper

= SUM(OFFSET(B4, -{2;1;0}, 0, {3;2;1}, 1))

 

[dispelthemyth]

I have also tried Index to Index like below which i would expect to output B2:B4; B3:B4; B4:B4 but again i get #value errors unless i wrap it with the Sum which then gets 90

= SUM(INDEX(OFFSET(B2, {0;1;2}, 0, 1, 1), 1, 1):INDEX(B4, 1, 1))

 

[Snakehips]

is your expected result a text string?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dispelthemyth]

is your expected result a text string?

Ideally, the output will be numbers, the formula will always be looking at the number of days in a month

 

[dispelthemyth]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am building the model for Office 365
Edit: Thanks, i have updated my signature, i didnt realise it was from a few years ago

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	Month
2	01/01/2022	31
3	01/02/2022	28
4	01/03/2022	31	{31;59;90}
5	01/04/2022	30	{30;61;89}
6	01/05/2022	31	{31;61;92}
7	01/06/2022	30	{30;61;91}
8	01/07/2022	31	{31;61;92}
9	01/08/2022	31	{31;62;92}
10	01/09/2022	30	{30;61;92}
11	01/10/2022	31	{31;61;92}
12	01/11/2022	30	{30;61;91}
13	01/12/2022	31	{31;61;92}
Data

Cell Formulas
Range	Formula
B2:B13	B2	=DAY(EOMONTH(A2,0))
C4:C13	C4	=ARRAYTOTEXT(MMULT(--(SEQUENCE(3)>=SEQUENCE(,3)),INDEX(B2:B4,{3;2;1})),1)

But the values are text & not numbers.

 

[Snakehips]

Similar with this

Book1
	A	B	C
1	Month	Days
2	Jan	31
3	Feb	28
4	Mar	31	{31:59:90}
5	Apr	30	{30:61:89}
6	May	31	{31:61:92}
7	Jun	30	{30:61:91}
8	Jul	31	{31:61:92}
9	Aug	31	{31:62:92}
10	Sep	30	{30:61:92}
11	Oct	31	{31:61:92}
12	Nov	30	{30:61:91}
13	Dec	31	{31:61:92}
Sheet2

Cell Formulas
Range	Formula
C4:C13	C4	="{"&TEXTJOIN(":",TRUE,B4,SUM(B3:B4),SUM(B2:B4))&"}"

 

[dispelthemyth]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	Month
2	01/01/2022	31
3	01/02/2022	28
4	01/03/2022	31	{31;59;90}
5	01/04/2022	30	{30;61;89}
6	01/05/2022	31	{31;61;92}
7	01/06/2022	30	{30;61;91}
8	01/07/2022	31	{31;61;92}
9	01/08/2022	31	{31;62;92}
10	01/09/2022	30	{30;61;92}
11	01/10/2022	31	{31;61;92}
12	01/11/2022	30	{30;61;91}
13	01/12/2022	31	{31;61;92}
Data

Cell Formulas
Range	Formula
B2:B13	B2	=DAY(EOMONTH(A2,0))
C4:C13	C4	=ARRAYTOTEXT(MMULT(--(SEQUENCE(3)>=SEQUENCE(,3)),INDEX(B2:B4,{3;2;1})),1)

But the values are text & not numbers.

Thank you very much, I believe this will give me exactly what i need, i can use the part inside the ArrayToText in my formula when i work on the next step of the model, you are a squire

= MMULT(--(SEQUENCE(3)>=SEQUENCE(,3)),INDEX(B2:B4,{3;2;1}))

 

[Fluff]

Glad we could help & thanks for the feedback.