Group sum based on the Ascending order of the number

[vasu2007]

Hello Experts,

I need to sum the numbers in col "B" based on the locations in Col "A", but is in increasing order.

COL#A COL#B
A - 1
A - 1
B - 2
B - 1
C - 3
C - 1

OUT PUT:
C(4)+B(3)+A(2) = 9

C+B+A should be in ascending order based on the sum in column B. This is the tricky part.
I have searched for this kind of requirement, but unable to find

Data & output is given in Below Picture.

Please help. Thanks in advance.

Consolidate.xlsx
	A	B	C	D	E	F	G	H
1	Location	Value			OUTPUT	SL NO	LOCATION	Sum
2	A	1				1	B+A+C	17.5
3	C	0.8				2	A+B+D+C	49.9
4	A	4.7				3	A+D+B+C	65.9
5	B	11
6		17.5
7	D	1.3
8	D	4.5
9	C	4.8
10	A	31.4
11	B	4.9
12	B	1.3
13	B	1.7
14		49.9
15	C	4.5
16	C	0.7
17	B	15.9
18	D	3.7
19	D	2.2
20	D	14.8
21	A	24.1
22		65.9
Sheet1

 

[becklog]

Is there a specific value range in col b? i.e LocationA-1 has a value of 1 to 10 Location A-2 has a value of 11 to 20 etc?

 

[RicoS]

How do you ascertain the Location codes (e.g. A+B+C)? The reason I ask, is if they were a result of another lookup or similar then it might be possible, but without doing a repeated SUMIF, with your version of Excel it would be difficult to get a useful answer!

=SUMPRODUCT($B$3:$B$22*($A$3:$A$22={"A","B","C"}))

This formula would work if you were hand typing in the Location IDs to the formula, but without VBA to auto-generate that formula it would be difficult to have it work each time a user entered location values. Excel 2019 doesn't recognise arrays typed into a cell unfortunately.

Edit: I should add, that if you had a limited number of locations, you could just add those to individual columns (i.e. columns labelled Location1, Location2...Location5 etc). Then the following would work:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Location	Value			OUTPUT	SL NO	LOCATION	Sum
2
3	A	1				1	A+B+C	A	B	C	None	106.8
4	C	0.8				2	A+B+D+C	A	B	D	C	133.3
5	A	4.7				3	A+D+B+C	A	D	B	C	133.3
6	B	11
7		17.5
8	D	1.3
9	D	4.5
10	C	4.8
11	A	31.4
12	B	4.9
13	B	1.3
14	B	1.7
15		49.9
16	C	4.5
17	C	0.7
18	B	15.9
19	D	3.7
20	D	2.2
21	D	14.8
22	A	24.1
Sheet1

Cell Formulas
Range	Formula
L3:L5	L3	=SUMPRODUCT($B$3:$B$22*($A$3:$A$22=H3:K3))

 

[vasu2007]

How do you ascertain the Location codes (e.g. A+B+C)? The reason I ask, is if they were a result of another lookup or similar then it might be possible, but without doing a repeated SUMIF, with your version of Excel it would be difficult to get a useful answer!

=SUMPRODUCT($B$3:$B$22*($A$3:$A$22={"A","B","C"}))

This formula would work if you were hand typing in the Location IDs to the formula, but without VBA to auto-generate that formula it would be difficult to have it work each time a user entered location values. Excel 2019 doesn't recognise arrays typed into a cell unfortunately.

Edit: I should add, that if you had a limited number of locations, you could just add those to individual columns (i.e. columns labelled Location1, Location2...Location5 etc). Then the following would work:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Location	Value			OUTPUT	SL NO	LOCATION	Sum
2
3	A	1				1	A+B+C	A	B	C	None	106.8
4	C	0.8				2	A+B+D+C	A	B	D	C	133.3
5	A	4.7				3	A+D+B+C	A	D	B	C	133.3
6	B	11
7		17.5
8	D	1.3
9	D	4.5
10	C	4.8
11	A	31.4
12	B	4.9
13	B	1.3
14	B	1.7
15		49.9
16	C	4.5
17	C	0.7
18	B	15.9
19	D	3.7
20	D	2.2
21	D	14.8
22	A	24.1
Sheet1

Cell Formulas
Range	Formula
L3:L5	L3	=SUMPRODUCT($B$3:$B$22*($A$3:$A$22=H3:K3))

Thank you for your response.
6th, 14th & 22nd rows are subtotals. or Where there is a blank cell in A column, that sum should be reflected against A+B+C.
ie., A,B,C locations should be concatenated & their corresponding sum should be reflected as shown in output.
It can be done by using VBA only as some complicated logic is involved.

 

[RicoS]

I think I understand what you're looking for now. My apologies. SL 1 refers to the first blank sum, SL 2 to the second blank sum, and so on. You want to return the blank row subtotals, and which locations that they subtotal (everything above/between the blank rows). That would be quite easy with unique and filter functions! I might get more time to look at it tomorrow, but it should be possible in Excel 2019 too.
I'm guessing that you can't receive the data in a different format, or access the original data source? That would always be my first suggestion.

 

[vasu2007]

Hello Experts,

I need to sum the numbers in col "B" based on the locations in Col "A", but is in increasing order.

COL#A COL#B
A - 1
A - 1
B - 2
B - 1
C - 3
C - 1

OUT PUT:
C(4)+B(3)+A(2) = 9

C+B+A should be in ascending order based on the sum in column B. This is the tricky part.
I have searched for this kind of requirement, but unable to find

Data & output is given in Below Picture.

Please help. Thanks in advance.

Consolidate.xlsx
	A	B	C	D	E	F	G	H
1	Location	Value			OUTPUT	SL NO	LOCATION	Sum
2	A	1				1	B+A+C	17.5
3	C	0.8				2	A+B+D+C	49.9
4	A	4.7				3	A+D+B+C	65.9
5	B	11
6		17.5
7	D	1.3
8	D	4.5
9	C	4.8
10	A	31.4
11	B	4.9
12	B	1.3
13	B	1.7
14		49.9
15	C	4.5
16	C	0.7
17	B	15.9
18	D	3.7
19	D	2.2
20	D	14.8
21	A	24.1
22		65.9
Sheet1

Hai Friends, Can some body help in writing the vba code please