SUM in changeable range between two texts

Unexpc · Feb 20, 2021

Hi guys
How set a range for sum that changeable and add cells or remove cells in this range...
And show the result just in one cell not a specific column for this, how can i do this?
(For example instead of =SUM(D1:D30) if in the D1 cell written CUST A and in D30 CUST, just sum between this two texts (and show just in one specific cell) even if in this range add cells or remove)

Saba Sabaratnam · Feb 20, 2021

you can use index formulas to attain this as demonstrated below.

Enter the following formula in H3

=sUM(INDEX(D:D,MATCH(G1,D:D,0)):INDEX(D:D,MATCH(G2,D:D,0)))

Kind regards

Saba

Unexpc · Feb 20, 2021

i can do like this between CUST B and CUST C and next CUST C and CUST D and....?

Unexpc · Feb 20, 2021

Saba Sabaratnam said:
you can use index formulas to attain this as demonstrated below.

Enter the following formula in H3

=sUM(INDEX(D:D,MATCH(G1,D:D,0)):INDEX(D:D,MATCH(G2,D:D,0)))

View attachment 32626

Kind regards

Saba

and another thing, i have CUST A and CUST B total sum in another sheet not in one sheet, what am i do?

Saba Sabaratnam · Feb 20, 2021

For your first question, yes it will work

For your question, you can use the following formula

SUM(INDEX(D:D,MATCH(G1,D:D,0)):D:D,Sheet2!D:D:INDEX(Sheet2!D:D,MATCH(Sheet2!G2,Sheet2!D:D,0)))

this formula assumes the starting point in Sheet 1 and ending point in Sheet 2.

Kind regards

Saba

Unexpc · Feb 23, 2021

Saba Sabaratnam said:
For your first question, yes it will work

For your question, you can use the following formula

SUM(INDEX(D:D,MATCH(G1,D:D,0)):D:D,Sheet2!D:D:INDEX(Sheet2!D:D,MATCH(Sheet2!G2,Sheet2!D:D,0)))

this formula assumes the starting point in Sheet 1 and ending point in Sheet 2.

Kind regards

Saba

i change this formula in my range but steel not work and just show formula in cell
SUM(INDEX(D:E,MATCH(N3,D:E,0)):D:E,Start!D:E:INDEX(Start!D:E,MATCH(Start!N4,Start!D:E,0)))
in Sheet that name Start show sub-total of between Customer 1 and Customer 2 their in Sheet that name Paper
and written this two name in N3 and N4 in Sheet Strat
What am i wrong?

Alex Blakenburg · Feb 26, 2021

Hello Unexp, I understand that you are bit frustrated getting a formula to do what you want to but the spreadsheet design commits a number of cardinal design sins that do not make it conducive to analysis.

Leveraging off what @Saba Sabaratnam did in response to your previous post and based on your latest attachments this should do what you want.
You will need to make the following corrections / modification to make it work:-

On Sheet 2
- Correct the spelling of Customer A (currently Cutomer)
- Add a last line which I have called "End of Customers" with the same row spacing (ei leave 1 blank row) as all your other Customer (A B C ..) headings
  This is to help the formula determine where the last valid customer finishes. The Row can be hidden, it should still work.
On Sheet 1
- In Column A list all the Customer references in Sheet 2 column A ie Customer A, B, C etc and including End of Customers. This last one can be hidden but it needs to be there to determine where to end the previous customer.

20210226 Sum from SubTotal to SubTotal.xlsx

A

B

C

D

1

Name

Date

Sum 1

Sum 2

2

Customer A

200114

764

26,542

3

Customer B

200114

2,184

113,130

4

Customer C

0

5

Other Customers

0

6

End of Customers

Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(Sheet2!B:B,MATCH($A3,Sheet2!A:A,0)-2,0),"")
C2:C5	C2	=IFERROR(SUM(INDEX(Sheet2!D:D,MATCH($A2,Sheet2!A:A,0)+1,0):INDEX(Sheet2!E:E,MATCH($A3,Sheet2!A:A,0)-2,0)),"")
D2:D5	D2	=IFERROR(SUM(INDEX(Sheet2!F:F,MATCH($A2,Sheet2!A:A,0)+1,0):INDEX(Sheet2!G:G,MATCH($A3,Sheet2!A:A,0)-2,0)),"")

The major spreadsheet design issues are:

Sheet 2 should be considered to be a data sheet and as such should have a column headed Customer and each line for each customer should have the Customer name on it ie all the Customer A lines should say "A" or customer A
Sheet 2 had merged cells on the row separating customers. Any experienced Excel user avoids merged cells like the plague.

Just those changes would make summarising the data a much simpler and more robust process and would allow the use of pivot tables which makes it more scalable as more customers are added.
It would also allow filtering to be applied to Sheet 2 should that be required.

Let me know if the formulas don't work for you or need explanation.

maabadi · Mar 4, 2021

I Test @Alex Blakenburg Method and its working. Only you need to add the same text at the end of Customer names at both sheet 1 & 2. if you don't want to see it change color of text to cell background color.
I add End of Customers to both sheets at the end of Both columns at both sheets.

Sheet2

Book1

A

B

C

D

E

F

G

1

Customer A

Date

FN

AG

BG

AC

BC

2

1

200101

1000

56

5656

3

2

200102

1001

47

4

3

200103

1002

58

5

4

200104

1003

22

6

5

200105

1004

34

888

7

6

200106

1005

56

8

7

200107

1006

55

9

8

200108

1007

78

5454

10

9

200109

1008

61

11

10

200110

1009

32

12

11

200111

1010

55

5656

13

12

200112

1011

66

14

13

200113

1012

88

15

14

200114

1013

56

8888

16

17

Customer B

Date

FN

AG

BG

AC

BC

18

1

200101

1000

88

19

2

200102

1001

666

20

3

200103

1002

33

21

4

200104

1003

25

22

5

200105

1004

645

23

6

200106

1005

221

56565

24

7

200107

1006

65

25

8

200108

1007

56

26

9

200109

1008

54

27

10

200110

1009

55

28

11

200111

1010

55

29

12

200112

1011

66

30

13

200113

1012

89

31

14

200114

1013

66

56565

32

33

Customer C

34

.

35

.

36

.

200104

37

38

End of Customers

Sheet2

Sheet1

Book1

A

B

C

D

1

Name

Date

Sum 1

Sum 2

2

Customer A

200114

764

26542

3

Customer B

200114

2184

113130

4

Customer C

200104

0

5

End of Customers

6

7

8

9

10

11

Last Formula I Have

LOOKUP(2,1/(Sheet2!B:B<>""),Sheet2!B:B)

SUM(Sheet2!D3:D1048576)+SUM(Sheet2!E3:E1048576) or SUM(Sheet2!D3:E1048576)

SUM(Sheet2!F3:G1048576)

12

This Formulas was for every sheet i create but i want in one sheet and between two customer

13

Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(Sheet2!B:B,MATCH($A3,Sheet2!A:A,0)-2,0),"")
C2:C5	C2	=IFERROR(SUM(INDEX(Sheet2!D:D,MATCH($A2,Sheet2!A:A,0)+1,0):INDEX(Sheet2!E:E,MATCH($A3,Sheet2!A:A,0)-2,0)),"")
D2:D5	D2	=IFERROR(SUM(INDEX(Sheet2!F:F,MATCH($A2,Sheet2!A:A,0)+1,0):INDEX(Sheet2!G:G,MATCH($A3,Sheet2!A:A,0)-2,0)),"")

Unexpc · Mar 4, 2021

maabadi said:
I Test @Alex Blakenburg Method and its working. Only you need to add the same text at the end of Customer names at both sheet 1 & 2. if you don't want to see it change color of text to cell background color.
I add End of Customers to both sheets at the end of Both columns at both sheets.

Sheet2

Book1
A B C D E F G
1 Customer A Date FN AG BG AC BC
2 1 200101 1000 56 5656
3 2 200102 1001 47
4 3 200103 1002 58
5 4 200104 1003 22
6 5 200105 1004 34 888
7 6 200106 1005 56
8 7 200107 1006 55
9 8 200108 1007 78 5454
10 9 200109 1008 61
11 10 200110 1009 32
12 11 200111 1010 55 5656
13 12 200112 1011 66
14 13 200113 1012 88
15 14 200114 1013 56 8888
16
17 Customer B Date FN AG BG AC BC
18 1 200101 1000 88
19 2 200102 1001 666
20 3 200103 1002 33
21 4 200104 1003 25
22 5 200105 1004 645
23 6 200106 1005 221 56565
24 7 200107 1006 65
25 8 200108 1007 56
26 9 200109 1008 54
27 10 200110 1009 55
28 11 200111 1010 55
29 12 200112 1011 66
30 13 200113 1012 89
31 14 200114 1013 66 56565
32
33 Customer C
34 .
35 .
36 . 200104
37
38 End of Customers
Sheet2

Sheet1

Book1
A B C D
1 Name Date Sum 1 Sum 2
2 Customer A 200114 764 26542
3 Customer B 200114 2184 113130
4 Customer C 200104 0 0
5 End of Customers
6
7
8
9
10
11 Last Formula I Have LOOKUP(2,1/(Sheet2!B:B<>""),Sheet2!B:B) SUM(Sheet2!D3:D1048576)+SUM(Sheet2!E3:E1048576) or SUM(Sheet2!D3:E1048576) SUM(Sheet2!F3:G1048576)
12 This Formulas was for every sheet i create but i want in one sheet and between two customer
13
Sheet1
Cell Formulas
Range Formula
B2:B5 B2 =IFERROR(INDEX(Sheet2!B:B,MATCH($A3,Sheet2!A:A,0)-2,0),"")
C2:C5 C2 =IFERROR(SUM(INDEX(Sheet2!D:D,MATCH($A2,Sheet2!A:A,0)+1,0):INDEX(Sheet2!E:E,MATCH($A3,Sheet2!A:A,0)-2,0)),"")
D2:D5 D2 =IFERROR(SUM(INDEX(Sheet2!F:F,MATCH($A2,Sheet2!A:A,0)+1,0):INDEX(Sheet2!G:G,MATCH($A3,Sheet2!A:A,0)-2,0)),"")

Yes you are right mabaadi im test again i find out i doing wrong in a part of formula
Thank You Alex Blakenburg, Great Work

maabadi · Mar 4, 2021

You're Welcome & Thanks for Feedback.

SUM in changeable range between two texts

Unexpc

Active Member

Excel Facts

Saba Sabaratnam

Active Member

Unexpc

Active Member

Unexpc

Active Member

Saba Sabaratnam

Active Member

Unexpc

Active Member

Alex Blakenburg

MrExcel MVP

maabadi

Well-known Member

Unexpc

Active Member

maabadi

Well-known Member

Similar threads

Forum statistics

Share this page

SUM in changeable range between two texts

Active Member

Excel Facts

Active Member

Active Member

Active Member

Active Member

Active Member

MrExcel MVP

Well-known Member

Active Member

Well-known Member

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