Textjoin and Sum the covered period for Google Sheets

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
343
Office Version
  1. 365
Platform
  1. Windows
I would like to textjoin the date every 1st and 4th times the name of the fruit appear then sum its qty and amount.


For example the apple. it appear on 1/7/24 (1st) then on 5/7/24 (4th) then sum the qty (2+3+3+9) and amount(16+45+44+43)
it appear again on 6/7/24 (1st) then on 10/7/24 (4th) then sum qty (8+2+9+5) and amount (12+41+19+32)


Date​
Name​
Qty​
Amount​
01-07-24​
Apple​
2​
16​
02-07-24​
Apple​
3​
45​
04-07-24​
Apple​
3​
44​
05-07-24​
Apple​
9​
43​
06-07-24​
Apple​
8​
12​
07-07-24​
Apple​
2​
41​
09-07-24​
Apple​
9​
19​
10-07-24​
Apple​
5​
32​



Book3
ABCDEFGHIJ
1DateNameQtyAmountExpected Result
201-07-24Apple216NameDate CoveredQtyAmount
301-07-24Banana235Apple1/7/24 - 5/7/2417148
401-07-24Cherry523Apple6/7/24 - 10/7/2424104
501-07-24Berry244Banana1/7/24 - 4/7/2415102
601-07-24Orange419Banana5/7/24 - 10/7/2426139
701-07-24Mangoe722Cherry1/7/24 - 4/7/2414138
802-07-24Apple345Cherry5/7/24 - 10/7/241475
902-07-24Banana719Berry1/7/24 - 4/7/2419121
1002-07-24Cherry350Berry5/7/24 - 9/7/2420115
1102-07-24Berry437Orange1/7/24 - 4/7/2418115
1202-07-24Orange346Mangoe1/7/24 - 5/7/2416115
1303-07-24Banana438
1403-07-24Cherry138
1503-07-24Berry729
1603-07-24Orange814
1703-07-24Mangoe127
1804-07-24Apple344
1904-07-24Banana210
2004-07-24Cherry527
2104-07-24Berry611
2204-07-24Orange336
2304-07-24Mangoe321
2405-07-24Apple943
2505-07-24Banana731
2605-07-24Cherry632
2705-07-24Berry642
2805-07-24Orange226
2905-07-24Mangoe545
3006-07-24Berry728
3106-07-24Orange431
3206-07-24Mangoe340
3306-07-24Apple812
3407-07-24Apple241
3507-07-24Banana623
3607-07-24Cherry310
3707-07-24Berry110
3807-07-24Orange941
3907-07-24Mangoe228
4008-07-24Mangoe1026
4109-07-24Apple919
4209-07-24Banana737
4309-07-24Cherry211
4409-07-24Berry635
4510-07-24Apple532
4610-07-24Banana648
4710-07-24Cherry322
4810-07-24Berry1014
Sheet7
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about
Excel Formula:
=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),DROP(REDUCE("",u,LAMBDA(a,b,LET(f,FILTER(A2:D100,B2:B100=b),VSTACK(a,DROP(REDUCE("",SEQUENCE(ROWS(f)/4,,0,4),LAMBDA(x,y,LET(t,TAKE(DROP(f,y),4),VSTACK(x,HSTACK(INDEX(t,1,2),TEXT(TAKE(t,1,1),"d/m/yy")&" - "&TEXT(TAKE(t,-1,1),"d/m/yy"),SUM(INDEX(t,,3)),SUM(INDEX(t,,4))))))),1))))),1))
 
Upvote 0
Solution
Try:

Book2
ABCDEFGHIJK
1DateNameQtyAmountExpected Result
201-07-24Apple216NameStart DateEnd DateQtyAmount
301-07-24Banana235Apple01-07-2405-07-2417148
401-07-24Cherry523Apple06-07-2410-07-2424104
501-07-24Berry244Banana01-07-2404-07-2415102
601-07-24Orange419Banana05-07-2410-07-2426139
701-07-24Mangoe722Berry01-07-2404-07-2419121
802-07-24Apple345Berry05-07-2409-07-2420115
902-07-24Banana719Berry10-07-2410-07-241014
1002-07-24Cherry350Cherry01-07-2404-07-2414138
1102-07-24Berry437Cherry05-07-2410-07-241475
1202-07-24Orange346Mangoe01-07-2405-07-2416115
1303-07-24Banana438Mangoe06-07-2408-07-241594
1403-07-24Cherry138Orange01-07-2404-07-2418115
1503-07-24Berry729Orange05-07-2407-07-241598
1603-07-24Orange814
1703-07-24Mangoe127
1804-07-24Apple344
1904-07-24Banana210
2004-07-24Cherry527
2104-07-24Berry611
2204-07-24Orange336
2304-07-24Mangoe321
2405-07-24Apple943
2505-07-24Banana731
2605-07-24Cherry632
2705-07-24Berry642
2805-07-24Orange226
2905-07-24Mangoe545
3006-07-24Berry728
3106-07-24Orange431
3206-07-24Mangoe340
3306-07-24Apple812
3407-07-24Apple241
3507-07-24Banana623
3607-07-24Cherry310
3707-07-24Berry110
3807-07-24Orange941
3907-07-24Mangoe228
4008-07-24Mangoe1026
4109-07-24Apple919
4209-07-24Banana737
4309-07-24Cherry211
4409-07-24Berry635
4510-07-24Apple532
4610-07-24Banana648
4710-07-24Cherry322
4810-07-24Berry1014
Sheet1
Cell Formulas
RangeFormula
G3:G15G3=LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=1)))
H3:H15H3=AGGREGATE(15,6,$A$2:$A$48/($B$2:$B$48=G3),COUNTIF(G$3:G3,G3)*4-3)
I3:I15I3=IFERROR(AGGREGATE(15,6,$A$2:$A$48/($B$2:$B$48=G3),COUNTIF(G$3:G3,G3)*4),MAXIFS($A$2:$A$48,$B$2:$B$48,G3))
J3:K15J3=SUMIFS(C$2:C$48,$B$2:$B$48,$G3,$A$2:$A$48,">="&$H3,$A$2:$A$48,"<="&$I3)
Dynamic array formulas.
 
Upvote 0
How about
Excel Formula:
=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),DROP(REDUCE("",u,LAMBDA(a,b,LET(f,FILTER(A2:D100,B2:B100=b),VSTACK(a,DROP(REDUCE("",SEQUENCE(ROWS(f)/4,,0,4),LAMBDA(x,y,LET(t,TAKE(DROP(f,y),4),VSTACK(x,HSTACK(INDEX(t,1,2),TEXT(TAKE(t,1,1),"d/m/yy")&" - "&TEXT(TAKE(t,-1,1),"d/m/yy"),SUM(INDEX(t,,3)),SUM(INDEX(t,,4))))))),1))))),1))
Do you have a method for assembling these formulas? Sometimes I can build up a formula from several other formulas, but it's a bit harder for me to take this formula apart and see what's happening at each step.
 
Upvote 0
Try:

Book2
ABCDEFGHIJK
1DateNameQtyAmountExpected Result
201-07-24Apple216NameStart DateEnd DateQtyAmount
301-07-24Banana235Apple01-07-2405-07-2417148
401-07-24Cherry523Apple06-07-2410-07-2424104
501-07-24Berry244Banana01-07-2404-07-2415102
601-07-24Orange419Banana05-07-2410-07-2426139
701-07-24Mangoe722Berry01-07-2404-07-2419121
802-07-24Apple345Berry05-07-2409-07-2420115
902-07-24Banana719Berry10-07-2410-07-241014
1002-07-24Cherry350Cherry01-07-2404-07-2414138
1102-07-24Berry437Cherry05-07-2410-07-241475
1202-07-24Orange346Mangoe01-07-2405-07-2416115
1303-07-24Banana438Mangoe06-07-2408-07-241594
1403-07-24Cherry138Orange01-07-2404-07-2418115
1503-07-24Berry729Orange05-07-2407-07-241598
1603-07-24Orange814
1703-07-24Mangoe127
1804-07-24Apple344
1904-07-24Banana210
2004-07-24Cherry527
2104-07-24Berry611
2204-07-24Orange336
2304-07-24Mangoe321
2405-07-24Apple943
2505-07-24Banana731
2605-07-24Cherry632
2705-07-24Berry642
2805-07-24Orange226
2905-07-24Mangoe545
3006-07-24Berry728
3106-07-24Orange431
3206-07-24Mangoe340
3306-07-24Apple812
3407-07-24Apple241
3507-07-24Banana623
3607-07-24Cherry310
3707-07-24Berry110
3807-07-24Orange941
3907-07-24Mangoe228
4008-07-24Mangoe1026
4109-07-24Apple919
4209-07-24Banana737
4309-07-24Cherry211
4409-07-24Berry635
4510-07-24Apple532
4610-07-24Banana648
4710-07-24Cherry322
4810-07-24Berry1014
Sheet1
Cell Formulas
RangeFormula
G3:G15G3=LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=1)))
H3:H15H3=AGGREGATE(15,6,$A$2:$A$48/($B$2:$B$48=G3),COUNTIF(G$3:G3,G3)*4-3)
I3:I15I3=IFERROR(AGGREGATE(15,6,$A$2:$A$48/($B$2:$B$48=G3),COUNTIF(G$3:G3,G3)*4),MAXIFS($A$2:$A$48,$B$2:$B$48,G3))
J3:K15J3=SUMIFS(C$2:C$48,$B$2:$B$48,$G3,$A$2:$A$48,">="&$H3,$A$2:$A$48,"<="&$I3)
Dynamic array formulas.
thank you very much sir for the classic approach..really appreciated.. but this one also extracts covered period that is less than 4 times
1721106461447.png
 
Upvote 0
How about
Excel Formula:
=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),DROP(REDUCE("",u,LAMBDA(a,b,LET(f,FILTER(A2:D100,B2:B100=b),VSTACK(a,DROP(REDUCE("",SEQUENCE(ROWS(f)/4,,0,4),LAMBDA(x,y,LET(t,TAKE(DROP(f,y),4),VSTACK(x,HSTACK(INDEX(t,1,2),TEXT(TAKE(t,1,1),"d/m/yy")&" - "&TEXT(TAKE(t,-1,1),"d/m/yy"),SUM(INDEX(t,,3)),SUM(INDEX(t,,4))))))),1))))),1))
works like a charm.. thanks man..really appreciated. if it’s not too much trouble, could you spare a moment to explore the possibility of converting this formula into a gsheet formula? thanks
 
Upvote 0
thank you very much sir for the classic approach..really appreciated.. but this one also extracts covered period that is less than 4 times
Sorry, I didn't realize that you didn't want partial periods. How about:

Book2
ABCDEFGHIJK
1DateNameQtyAmountExpected Result
201-07-24Apple216NameStart DateEnd DateQtyAmount
301-07-24Banana235Apple01-07-2405-07-2417148
401-07-24Cherry523Apple06-07-2410-07-2424104
501-07-24Berry244Banana01-07-2404-07-2415102
601-07-24Orange419Banana05-07-2410-07-2426139
701-07-24Mangoe722Berry01-07-2404-07-2419121
802-07-24Apple345Berry05-07-2409-07-2420115
902-07-24Banana719Cherry01-07-2404-07-2414138
1002-07-24Cherry350Cherry05-07-2410-07-241475
1102-07-24Berry437Mangoe01-07-2405-07-2416115
1202-07-24Orange346Orange01-07-2404-07-2418115
1303-07-24Banana438
1403-07-24Cherry138
1503-07-24Berry729
1603-07-24Orange814
1703-07-24Mangoe127
1804-07-24Apple344
1904-07-24Banana210
2004-07-24Cherry527
2104-07-24Berry611
2204-07-24Orange336
2304-07-24Mangoe321
2405-07-24Apple943
2505-07-24Banana731
2605-07-24Cherry632
2705-07-24Berry642
2805-07-24Orange226
2905-07-24Mangoe545
3006-07-24Berry728
3106-07-24Orange431
3206-07-24Mangoe340
3306-07-24Apple812
3407-07-24Apple241
3507-07-24Banana623
3607-07-24Cherry310
3707-07-24Berry110
3807-07-24Orange941
3907-07-24Mangoe228
4008-07-24Mangoe1026
4109-07-24Apple919
4209-07-24Banana737
4309-07-24Cherry211
4409-07-24Berry635
4510-07-24Apple532
4610-07-24Banana648
4710-07-24Cherry322
4810-07-24Berry1014
49
Sheet1
Cell Formulas
RangeFormula
G3:G12G3=LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=0)))
H3:I12H3=LET(d,A2:A48,n,B2:B48,ds,SORTBY(d,n),ns,SORT(n),c,COUNTIF(OFFSET(G3#,0,0,SEQUENCE(ROWS(G3#))),G3#),m,MATCH(G3#,ns,0),INDEX(ds,m+c*4-{4,1}))
J3:K12J3=LET(d,A2:A48,n,B2:B48,q,C2:C48,a,D2:D48,s,INDEX(H3#,0,1),e,INDEX(H3#,0,2),CHOOSE({1,2},SUMIFS(q,n,G3#,d,">="&s,d,"<="&e),SUMIFS(a,n,G3#,d,">="&s,d,"<="&e)))
Dynamic array formulas.


For this version, I used SPILL formulas so you don't have to drag them down. I find in many cases that it's easier to maintain multiple formulas than one large formula. These formulas can probably be improved on, since I'm using Excel 2021 and I don't have some of the newer functions. They also use OFFSET which is volatile.

I don't have access to gsheets, so I can't help you out there.
 
Upvote 1
Sorry, I didn't realize that you didn't want partial periods. How about:

Book2
ABCDEFGHIJK
1DateNameQtyAmountExpected Result
201-07-24Apple216NameStart DateEnd DateQtyAmount
301-07-24Banana235Apple01-07-2405-07-2417148
401-07-24Cherry523Apple06-07-2410-07-2424104
501-07-24Berry244Banana01-07-2404-07-2415102
601-07-24Orange419Banana05-07-2410-07-2426139
701-07-24Mangoe722Berry01-07-2404-07-2419121
802-07-24Apple345Berry05-07-2409-07-2420115
902-07-24Banana719Cherry01-07-2404-07-2414138
1002-07-24Cherry350Cherry05-07-2410-07-241475
1102-07-24Berry437Mangoe01-07-2405-07-2416115
1202-07-24Orange346Orange01-07-2404-07-2418115
1303-07-24Banana438
1403-07-24Cherry138
1503-07-24Berry729
1603-07-24Orange814
1703-07-24Mangoe127
1804-07-24Apple344
1904-07-24Banana210
2004-07-24Cherry527
2104-07-24Berry611
2204-07-24Orange336
2304-07-24Mangoe321
2405-07-24Apple943
2505-07-24Banana731
2605-07-24Cherry632
2705-07-24Berry642
2805-07-24Orange226
2905-07-24Mangoe545
3006-07-24Berry728
3106-07-24Orange431
3206-07-24Mangoe340
3306-07-24Apple812
3407-07-24Apple241
3507-07-24Banana623
3607-07-24Cherry310
3707-07-24Berry110
3807-07-24Orange941
3907-07-24Mangoe228
4008-07-24Mangoe1026
4109-07-24Apple919
4209-07-24Banana737
4309-07-24Cherry211
4409-07-24Berry635
4510-07-24Apple532
4610-07-24Banana648
4710-07-24Cherry322
4810-07-24Berry1014
49
Sheet1
Cell Formulas
RangeFormula
G3:G12G3=LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=0)))
H3:I12H3=LET(d,A2:A48,n,B2:B48,ds,SORTBY(d,n),ns,SORT(n),c,COUNTIF(OFFSET(G3#,0,0,SEQUENCE(ROWS(G3#))),G3#),m,MATCH(G3#,ns,0),INDEX(ds,m+c*4-{4,1}))
J3:K12J3=LET(d,A2:A48,n,B2:B48,q,C2:C48,a,D2:D48,s,INDEX(H3#,0,1),e,INDEX(H3#,0,2),CHOOSE({1,2},SUMIFS(q,n,G3#,d,">="&s,d,"<="&e),SUMIFS(a,n,G3#,d,">="&s,d,"<="&e)))
Dynamic array formulas.


For this version, I used SPILL formulas so you don't have to drag them down. I find in many cases that it's easier to maintain multiple formulas than one large formula. These formulas can probably be improved on, since I'm using Excel 2021 and I don't have some of the newer functions. They also use OFFSET which is volatile.

I don't have access to gsheets, so I can't help you out there.
thanks man it worked as well..really appreciate it..
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top