Textjoin and Sum the covered period for Google Sheets

ExcelNewbie2020 · Jul 15, 2024

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

A

B

C

D

E

F

G

H

I

J

1

Date

Name

Qty

Amount

Expected Result

2

01-07-24

Apple

2

16

Name

Date Covered

Qty

Amount

3

01-07-24

Banana

2

35

Apple

1/7/24 - 5/7/24

17

148

4

01-07-24

Cherry

5

23

Apple

6/7/24 - 10/7/24

24

104

5

01-07-24

Berry

2

44

Banana

1/7/24 - 4/7/24

15

102

6

01-07-24

Orange

4

19

Banana

5/7/24 - 10/7/24

26

139

7

01-07-24

Mangoe

7

22

Cherry

1/7/24 - 4/7/24

14

138

8

02-07-24

Apple

3

45

Cherry

5/7/24 - 10/7/24

14

75

9

02-07-24

Banana

7

19

Berry

1/7/24 - 4/7/24

19

121

10

02-07-24

Cherry

3

50

Berry

5/7/24 - 9/7/24

20

115

11

02-07-24

Berry

4

37

Orange

1/7/24 - 4/7/24

18

115

12

02-07-24

Orange

3

46

Mangoe

1/7/24 - 5/7/24

16

115

13

03-07-24

Banana

4

38

14

03-07-24

Cherry

1

38

15

03-07-24

Berry

7

29

16

03-07-24

Orange

8

14

17

03-07-24

Mangoe

1

27

18

04-07-24

Apple

3

44

19

04-07-24

Banana

2

10

20

04-07-24

Cherry

5

27

21

04-07-24

Berry

6

11

22

04-07-24

Orange

3

36

23

04-07-24

Mangoe

3

21

24

05-07-24

Apple

9

43

25

05-07-24

Banana

7

31

26

05-07-24

Cherry

6

32

27

05-07-24

Berry

6

42

28

05-07-24

Orange

2

26

29

05-07-24

Mangoe

5

45

30

06-07-24

Berry

7

28

31

06-07-24

Orange

4

31

32

06-07-24

Mangoe

3

40

33

06-07-24

Apple

8

12

34

07-07-24

Apple

2

41

35

07-07-24

Banana

6

23

36

07-07-24

Cherry

3

10

37

07-07-24

Berry

1

10

38

07-07-24

Orange

9

41

39

07-07-24

Mangoe

2

28

40

08-07-24

Mangoe

10

26

41

09-07-24

Apple

9

19

42

09-07-24

Banana

7

37

43

09-07-24

Cherry

2

11

44

09-07-24

Berry

6

35

45

10-07-24

Apple

5

32

46

10-07-24

Banana

6

48

47

10-07-24

Cherry

3

22

48

10-07-24

Berry

10

14

Sheet7

Fluff · Jul 15, 2024

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

Eric W · Jul 15, 2024

Try:

Book2

A

B

C

D

E

F

G

H

I

J

K

1

Date

Name

Qty

Amount

Expected Result

2

01-07-24

Apple

2

16

Name

Start Date

End Date

Qty

Amount

3

01-07-24

Banana

2

35

4

01-07-24

Cherry

5

23

Apple

5

01-07-24

Berry

2

44

Banana

6

01-07-24

Orange

4

19

Banana

7

01-07-24

Mangoe

7

22

Berry

8

02-07-24

Apple

3

45

Berry

9

02-07-24

Banana

7

19

Berry

10-07-24

10

14

10

02-07-24

Cherry

3

50

Cherry

11

02-07-24

Berry

4

37

Cherry

12

02-07-24

Orange

3

46

Mangoe

13

03-07-24

Banana

4

38

Mangoe

14

03-07-24

Cherry

1

38

Orange

15

03-07-24

Berry

7

29

Orange

16

03-07-24

Orange

8

14

17

03-07-24

Mangoe

1

27

18

04-07-24

Apple

3

44

19

04-07-24

Banana

2

10

20

04-07-24

Cherry

5

27

21

04-07-24

Berry

6

11

22

04-07-24

Orange

3

36

23

04-07-24

Mangoe

3

21

24

05-07-24

Apple

9

43

25

05-07-24

Banana

7

31

26

05-07-24

Cherry

6

32

27

05-07-24

Berry

6

42

28

05-07-24

Orange

2

26

29

05-07-24

Mangoe

5

45

30

06-07-24

Berry

7

28

31

06-07-24

Orange

4

31

32

06-07-24

Mangoe

3

40

33

06-07-24

Apple

8

12

34

07-07-24

Apple

2

41

35

07-07-24

Banana

6

23

36

07-07-24

Cherry

3

10

37

07-07-24

Berry

1

10

38

07-07-24

Orange

9

41

39

07-07-24

Mangoe

2

28

40

08-07-24

Mangoe

10

26

41

09-07-24

Apple

9

19

42

09-07-24

Banana

7

37

43

09-07-24

Cherry

2

11

44

09-07-24

Berry

6

35

45

10-07-24

Apple

5

32

46

10-07-24

Banana

6

48

47

10-07-24

Cherry

3

22

48

10-07-24

Berry

10

14

Sheet1

Cell Formulas
Range	Formula
G3:G15	G3	=LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=1)))
H3:H15	H3	=AGGREGATE(15,6,$A$2:$A$48/($B$2:$B$48=G3),COUNTIF(G$3:G3,G3)*4-3)
I3:I15	I3	=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:K15	J3	=SUMIFS(C$2:C$48,$B$2:$B$48,$G3,$A$2:$A$48,">="&$H3,$A$2:$A$48,"<="&$I3)
Dynamic array formulas.

Vogateer · Jul 15, 2024

Fluff said:

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.

Fluff · Jul 15, 2024

Vogateer said:
Do you have a method for assembling these formulas?

Not really.

ExcelNewbie2020 · Jul 16, 2024

Eric W said:
Try:

Book2
A B C D E F G H I J K
1 Date Name Qty Amount Expected Result
2 01-07-24 Apple 2 16 Name Start Date End Date Qty Amount
3 01-07-24 Banana 2 35 Apple 01-07-24 05-07-24 17 148
4 01-07-24 Cherry 5 23 Apple 06-07-24 10-07-24 24 104
5 01-07-24 Berry 2 44 Banana 01-07-24 04-07-24 15 102
6 01-07-24 Orange 4 19 Banana 05-07-24 10-07-24 26 139
7 01-07-24 Mangoe 7 22 Berry 01-07-24 04-07-24 19 121
8 02-07-24 Apple 3 45 Berry 05-07-24 09-07-24 20 115
9 02-07-24 Banana 7 19 Berry 10-07-24 10-07-24 10 14
10 02-07-24 Cherry 3 50 Cherry 01-07-24 04-07-24 14 138
11 02-07-24 Berry 4 37 Cherry 05-07-24 10-07-24 14 75
12 02-07-24 Orange 3 46 Mangoe 01-07-24 05-07-24 16 115
13 03-07-24 Banana 4 38 Mangoe 06-07-24 08-07-24 15 94
14 03-07-24 Cherry 1 38 Orange 01-07-24 04-07-24 18 115
15 03-07-24 Berry 7 29 Orange 05-07-24 07-07-24 15 98
16 03-07-24 Orange 8 14
17 03-07-24 Mangoe 1 27
18 04-07-24 Apple 3 44
19 04-07-24 Banana 2 10
20 04-07-24 Cherry 5 27
21 04-07-24 Berry 6 11
22 04-07-24 Orange 3 36
23 04-07-24 Mangoe 3 21
24 05-07-24 Apple 9 43
25 05-07-24 Banana 7 31
26 05-07-24 Cherry 6 32
27 05-07-24 Berry 6 42
28 05-07-24 Orange 2 26
29 05-07-24 Mangoe 5 45
30 06-07-24 Berry 7 28
31 06-07-24 Orange 4 31
32 06-07-24 Mangoe 3 40
33 06-07-24 Apple 8 12
34 07-07-24 Apple 2 41
35 07-07-24 Banana 6 23
36 07-07-24 Cherry 3 10
37 07-07-24 Berry 1 10
38 07-07-24 Orange 9 41
39 07-07-24 Mangoe 2 28
40 08-07-24 Mangoe 10 26
41 09-07-24 Apple 9 19
42 09-07-24 Banana 7 37
43 09-07-24 Cherry 2 11
44 09-07-24 Berry 6 35
45 10-07-24 Apple 5 32
46 10-07-24 Banana 6 48
47 10-07-24 Cherry 3 22
48 10-07-24 Berry 10 14
Sheet1
Cell Formulas
Range Formula
G3:G15 G3 =LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=1)))
H3:H15 H3 =AGGREGATE(15,6,$A$2:$A$48/($B$2:$B$48=G3),COUNTIF(G$3:G3,G3)*4-3)
I3:I15 I3 =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:K15 J3 =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

ExcelNewbie2020 · Jul 16, 2024

Fluff said:

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

Eric W · Jul 16, 2024

ExcelNewbie2020 said:
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

A

B

C

D

E

F

G

H

I

J

K

1

Date

Name

Qty

Amount

Expected Result

2

01-07-24

Apple

2

16

Name

Start Date

End Date

Qty

Amount

3

01-07-24

Banana

2

35

Apple

01-07-24

05-07-24

17

148

4

01-07-24

Cherry

5

23

Apple

06-07-24

10-07-24

24

104

5

01-07-24

Berry

2

44

Banana

01-07-24

04-07-24

15

102

6

01-07-24

Orange

4

19

Banana

05-07-24

10-07-24

26

139

7

01-07-24

Mangoe

7

22

Berry

01-07-24

04-07-24

19

121

8

02-07-24

Apple

3

45

Berry

05-07-24

09-07-24

20

115

9

02-07-24

Banana

7

19

Cherry

01-07-24

04-07-24

14

138

10

02-07-24

Cherry

3

50

Cherry

05-07-24

10-07-24

14

75

11

02-07-24

Berry

4

37

Mangoe

01-07-24

05-07-24

16

115

12

02-07-24

Orange

3

46

Orange

01-07-24

04-07-24

18

115

13

03-07-24

Banana

4

38

14

03-07-24

Cherry

1

38

15

03-07-24

Berry

7

29

16

03-07-24

Orange

8

14

17

03-07-24

Mangoe

1

27

18

04-07-24

Apple

3

44

19

04-07-24

Banana

2

10

20

04-07-24

Cherry

5

27

21

04-07-24

Berry

6

11

22

04-07-24

Orange

3

36

23

04-07-24

Mangoe

3

21

24

05-07-24

Apple

9

43

25

05-07-24

Banana

7

31

26

05-07-24

Cherry

6

32

27

05-07-24

Berry

6

42

28

05-07-24

Orange

2

26

29

05-07-24

Mangoe

5

45

30

06-07-24

Berry

7

28

31

06-07-24

Orange

4

31

32

06-07-24

Mangoe

3

40

33

06-07-24

Apple

8

12

34

07-07-24

Apple

2

41

35

07-07-24

Banana

6

23

36

07-07-24

Cherry

3

10

37

07-07-24

Berry

1

10

38

07-07-24

Orange

9

41

39

07-07-24

Mangoe

2

28

40

08-07-24

Mangoe

10

26

41

09-07-24

Apple

9

19

42

09-07-24

Banana

7

37

43

09-07-24

Cherry

2

11

44

09-07-24

Berry

6

35

45

10-07-24

Apple

5

32

46

10-07-24

Banana

6

48

47

10-07-24

Cherry

3

22

48

10-07-24

Berry

10

14

49

Sheet1

Cell Formulas
Range	Formula
G3:G12	G3	=LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=0)))
H3:I12	H3	=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:K12	J3	=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.

ExcelNewbie2020 · Jul 16, 2024

Eric W said:
Sorry, I didn't realize that you didn't want partial periods. How about:

Book2
A B C D E F G H I J K
1 Date Name Qty Amount Expected Result
2 01-07-24 Apple 2 16 Name Start Date End Date Qty Amount
3 01-07-24 Banana 2 35 Apple 01-07-24 05-07-24 17 148
4 01-07-24 Cherry 5 23 Apple 06-07-24 10-07-24 24 104
5 01-07-24 Berry 2 44 Banana 01-07-24 04-07-24 15 102
6 01-07-24 Orange 4 19 Banana 05-07-24 10-07-24 26 139
7 01-07-24 Mangoe 7 22 Berry 01-07-24 04-07-24 19 121
8 02-07-24 Apple 3 45 Berry 05-07-24 09-07-24 20 115
9 02-07-24 Banana 7 19 Cherry 01-07-24 04-07-24 14 138
10 02-07-24 Cherry 3 50 Cherry 05-07-24 10-07-24 14 75
11 02-07-24 Berry 4 37 Mangoe 01-07-24 05-07-24 16 115
12 02-07-24 Orange 3 46 Orange 01-07-24 04-07-24 18 115
13 03-07-24 Banana 4 38
14 03-07-24 Cherry 1 38
15 03-07-24 Berry 7 29
16 03-07-24 Orange 8 14
17 03-07-24 Mangoe 1 27
18 04-07-24 Apple 3 44
19 04-07-24 Banana 2 10
20 04-07-24 Cherry 5 27
21 04-07-24 Berry 6 11
22 04-07-24 Orange 3 36
23 04-07-24 Mangoe 3 21
24 05-07-24 Apple 9 43
25 05-07-24 Banana 7 31
26 05-07-24 Cherry 6 32
27 05-07-24 Berry 6 42
28 05-07-24 Orange 2 26
29 05-07-24 Mangoe 5 45
30 06-07-24 Berry 7 28
31 06-07-24 Orange 4 31
32 06-07-24 Mangoe 3 40
33 06-07-24 Apple 8 12
34 07-07-24 Apple 2 41
35 07-07-24 Banana 6 23
36 07-07-24 Cherry 3 10
37 07-07-24 Berry 1 10
38 07-07-24 Orange 9 41
39 07-07-24 Mangoe 2 28
40 08-07-24 Mangoe 10 26
41 09-07-24 Apple 9 19
42 09-07-24 Banana 7 37
43 09-07-24 Cherry 2 11
44 09-07-24 Berry 6 35
45 10-07-24 Apple 5 32
46 10-07-24 Banana 6 48
47 10-07-24 Cherry 3 22
48 10-07-24 Berry 10 14
49
Sheet1
Cell Formulas
Range Formula
G3:G12 G3 =LET(r,B2:B48,SORT(FILTER(r,MOD(COUNTIF(OFFSET(r,0,0,SEQUENCE(ROWS(r))),r),4)=0)))
H3:I12 H3 =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:K12 J3 =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..

Fluff · Jul 16, 2024

ExcelNewbie2020 said:
could you spare a moment to explore the possibility of converting this formula into a gsheet formula?

If you need a formula for Google Sheets, then you should make that clear right from the start & such questions should be posted in the General Discussion & Other Applications section of the board.

Textjoin and Sum the covered period for Google Sheets

ExcelNewbie2020

Active Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Eric W

MrExcel MVP

Vogateer

Board Regular

Fluff

MrExcel MVP, Moderator

ExcelNewbie2020

Active Member

ExcelNewbie2020

Active Member

Eric W

MrExcel MVP

ExcelNewbie2020

Active Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Textjoin and Sum the covered period for Google Sheets

Active Member

Excel Facts

MrExcel MVP, Moderator

MrExcel MVP

Board Regular

MrExcel MVP, Moderator

Active Member

Active Member

MrExcel MVP

Active Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock