Sumifs or VBA?

[Razzy]

Col A (variables) and Col B (sum values).


Keyword: BA-U01

*Variables in this order.

Eksample:

BA-U04

BA-U03

BA-U02

BA-U01

BA-E01

BA-E02

BA-E03

BA-E04

BA-E05

BA-E06

BA-E07

BA-E08

BA-E09

BA-E10

BA-E11

BA-E12

BA-E13

BA-E14

BA-E15

BA-E16

BA-E17

BA-E18

BA-E19

BA-E20

BB-U04

BB-U03

BB-U02

BB-U01

BB-E01

BB-E02

BB-E03

BB-E04

BB-E05

BB-E06

BB-E07

BB-E08

BB-E09

BB-E10

BB-E11

BB-E12

BB-E13

BB-E14

BB-E15

BB-E16

BB-E17

BB-E18

BB-E19

BB-E20

Task:
If I give you BA-E05, how to get everything summed in Col B, from variable and down?

I've messed around with SUMIFS(Col B, Col A, LEFT(text,FIND(character,text)-1)&"*") [sums all for example BA-*]

Then I need take minus where I am (easy vlookup BA-E05) and minus everything over (hard part!) BA-E05 (BA-E04, 3, 2,...)


VBA? xD

 

[jasonb75]

Perhaps

=SUM(INDEX(B:B,MATCH("BA-E05",A:A,0)):INDEX(B:B,MATCH("zzzz",A:A,1)))

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
1
2	BA-U04	1		BA-E05	1140
3	BA-U03	2		BB-E08	546
4	BA-U02	3
5	BA-U01	4
6	BA-E01	5
7	BA-E02	6
8	BA-E03	7
9	BA-E04	8
10	BA-E05	9	1140
11	BA-E06	10
12	BA-E07	11
13	BA-E08	12
14	BA-E09	13
15	BA-E10	14
16	BA-E11	15
17	BA-E12	16
18	BA-E13	17
19	BA-E14	18
20	BA-E15	19
21	BA-E16	20
22	BA-E17	21
23	BA-E18	22
24	BA-E19	23
25	BA-E20	24
26	BB-U04	25
27	BB-U03	26
28	BB-U02	27
29	BB-U01	28
30	BB-E01	29
31	BB-E02	30
32	BB-E03	31
33	BB-E04	32
34	BB-E05	33
35	BB-E06	34
36	BB-E07	35
37	BB-E08	36	546
38	BB-E09	37
39	BB-E10	38
40	BB-E11	39
41	BB-E12	40
42	BB-E13	41
43	BB-E14	42
44	BB-E15	43
45	BB-E16	44
46	BB-E17	45
47	BB-E18	46
48	BB-E19	47
49	BB-E20	48
50
Lists

Cell Formulas
Range	Formula
E2:E3	E2	=SUM(INDEX($B$2:$B$49,MATCH(D2,$A$2:$A$49,0)):B$49)
C10,C37	C10	=SUM(B10:B$49)

In future please use the XL2BB add-in to post data to the board. Thanks

 

[Razzy]

In future please use the XL2BB add-in to post data to the board. Thanks

You are one gifted soul! This works very well!

I will use XL2BB - thx!

 

[Razzy]

How about

I wonder why I can't limist the match search to only BA- og BB- and so on by replacing "D2":

=SUM(INDEX($B$2:$B$49,MATCH(D2,$A$2:$A$49,0)):B$49)

With: LEFT(D2, FIND(E)-1)&"*") gives for example: BA-

=SUM(INDEX($B$2:$B$49,MATCH(LEFT(D2, FIND(E)-1)&"*"),$A$2:$A$49,0)):B$49)

If this work I only sum inside the same category BA og BB.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
2	BA-U04	1		BA-E05	264
3	BA-U03	2		BA-E05	264
4	BA-U02	3
5	BA-U01	4
6	BA-E01	5
7	BA-E02	6
8	BA-E03	7
9	BA-E04	8
10	BA-E05	9
11	BA-E06	10
12	BA-E07	11
13	BA-E08	12
14	BA-E09	13
15	BA-E10	14
16	BA-E11	15
17	BA-E12	16
18	BA-E13	17
19	BA-E14	18
20	BA-E15	19
21	BA-E16	20
22	BA-E17	21
23	BA-E18	22
24	BA-E19	23
25	BA-E20	24
26	BB-U04	25
27	BB-U03	26
28	BB-U02	27
29	BB-U01	28
30	BB-E01	29
31	BB-E02	30
32	BB-E03	31
33	BB-E04	32
34	BB-E05	33
35	BB-E06	34
36	BB-E07	35
37	BB-E08	36
38	BB-E09	37
39	BB-E10	38
40	BB-E11	39
41	BB-E12	40
42	BB-E13	41
43	BB-E14	42
44	BB-E15	43
45	BB-E16	44
46	BB-E17	45
47	BB-E18	46
48	BB-E19	47
49	BB-E20	48
Lists

Cell Formulas
Range	Formula
E2	E2	=SUM(FILTER(INDEX($B$2:$B$49,MATCH(D2,$A$2:$A$49,0)):B$49,LEFT(INDEX($A$2:$A$49,MATCH(D2,$A$2:$A$49,0)):A$49,2)=LEFT(D2,2)))
E3	E3	=SUMPRODUCT((LEFT(INDEX($A$2:$A$49,MATCH(D3,$A$2:$A$49,0)):A$49,2)=LEFT(D3,2))*(INDEX($B$2:$B$49,MATCH(D3,$A$2:$A$49,0)):B$49))

 

[Razzy]

How about

I salute you, and I am in your debt - the formulas worked flawlessly

 

[Fluff]

Glad we could help & thanks for the feedback