Sumifs or index match

N0t Y0urs · May 31, 2022

Hi all

I have 15 columns of data with 25 rows each. The columns are broken into sets of 3 therefore giving me 5 different groups. These are number 1 - 5, while my rows are numbered 5, 10, … 500, and my columns are labelled FWD, DB and RB

Each group have the same columns but the values are different so what I want to do and get confused is the following:

If my criteria is 3 and 200 I would like to calculate the sum of FWD for the first 3 groups from the first row to the row labelled 200. It would be the same formula for the DB columns but for the RB column it would just be the sum of that column on row labelled 200.

I can get it to work for just one column but when I add the groups in I get confused.

This is what I currently have:

This is for my FWD and DB columns:
=INDEX($D$9:$D$27,MATCH($C$59,$B$9:$B$27))

Where D is group 1 DB, C is my second criteria and B is the row value that criteria is looking for

Then for the FWD column it’s the same only D becomes C

Then my RB column formula is:
=INDEX($E$9:$E$27,MATCH($C$59,$B$9:$B$27))

RB is column E.

So my sheet has these columns:

FWD: C, G, K, O and S
DB: D, H, L, P and T
RB: E, I, M, Q and U

Hope that makes sense as my working file is large. I’m working with 365 and Google sheets.

Thanks in advance

kweaver · Jun 1, 2022

It might help if you posted a sample of the data using XL2BB.

N0t Y0urs · Jun 1, 2022

Here is a link as I am pulling data from multiple sheets.

Also it’s the con sheet I am currently working on

Broken

Dash 1 200 Per Day,DB,RB,Total 5,Fri 22-July 10,Fri 24-June 15,Fri 17-June 20,Fri 10-June 25,Wed 8-June Variable,Fri 10-June 5,Start,Profit,RB,Daily %,FWD,DB,RB,Difference Acc1, $ 68.00 , $ 2.00 , $ 70.00 ,2.9%, $ - , $ - , $ 174.61 , $ 104.61 Acc2, $ 125.00 , $ 125.00 ,0.0%, $ - , $ - ...

docs.google.com

Thanks

N0t Y0urs · Jun 1, 2022

I have continued playing with this and I have gotten this far

=IFS($C$58=5,SUM(INDEX($C$3:$C$27,MATCH($C$59,$B$3:$B$27)),INDEX($G$3:$G$27,MATCH($C$59,$B$3:$B$27)),INDEX($K$3:$K$27,MATCH($C$59,$B$3:$B$27)),INDEX($O$3:$O$27,MATCH($C$59,$B$3:$B$27)),INDEX($S$3:$S$27,MATCH($C$59,$B$3:$B$27))),$C$58=4,SUM(INDEX($C$3:$C$27,MATCH($C$59,$B$3:$B$27)),INDEX($G$3:$G$27,MATCH($C$59,$B$3:$B$27)),INDEX($K$3:$K$27,MATCH($C$59,$B$3:$B$27)),INDEX($O$3:$O$27,MATCH($C$59,$B$3:$B$27))))

It gives me the correct results for 4 and 5 but the moment I add 3 in goes wrong.

I feel I’ve over complicated this

Marcelo Branco · Jun 1, 2022

Maybe something like this

Observe the group numbers in all columns (in gray)

Pasta1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

2

3

4

5

Code

Groups

Row

Result

2

FWD

DB

RB

FWD

DB

RB

FWD

DB

RB

FWD

DB

RB

FWD

DB

RB

FWD

3

200

115

3

5

2

4

3

5

2

1

3

5

4

3

DB

3

200

107

4

10

4

3

1

5

2

3

1

5

2

4

5

4

3

2

RB

3

200

11

5

15

4

1

5

4

5

2

3

2

5

4

2

4

1

4

6

20

3

1

5

3

4

1

3

5

1

3

1

2

4

7

30

1

4

3

5

2

3

2

1

4

1

2

3

4

2

8

40

5

2

4

2

1

2

3

2

5

2

9

50

5

1

3

1

2

5

3

1

3

10

75

1

3

4

3

2

1

2

5

2

4

3

4

3

11

100

4

2

1

2

5

3

4

3

1

5

1

2

12

125

4

1

5

1

2

3

5

3

1

5

2

4

1

13

150

1

3

5

2

3

4

1

3

5

2

1

5

14

175

5

3

4

2

4

5

1

2

3

2

1

15

200

3

4

1

2

5

4

5

3

4

3

2

5

4

16

225

4

3

1

4

3

2

4

5

3

2

1

17

250

1

2

1

2

1

5

1

3

2

3

4

1

5

1

18

275

1

2

1

2

5

1

2

4

1

5

3

19

300

1

2

5

1

4

5

2

3

2

3

1

4

20

325

3

1

3

2

1

3

4

2

1

4

3

4

21

350

5

2

4

2

4

3

5

1

4

3

4

22

375

1

4

3

5

1

4

5

2

5

4

2

3

4

23

400

2

4

3

5

2

5

4

2

1

3

1

5

3

24

425

2

4

1

3

4

5

2

3

5

1

5

4

25

450

5

1

3

4

2

3

2

3

1

2

5

1

3

26

475

1

5

1

3

1

3

1

5

4

27

500

4

1

3

1

4

1

3

1

3

4

1

3

4

Plan4

Cell Formulas
Range	Formula
Z2:Z4	Z2	=IF(W2="RB",SUMPRODUCT(($B$3:$B$27=Y2)($C$1:$U$1<=X2)($C$2:$U$2=W2),$C$3:$U$27),SUMPRODUCT(($B$3:$B$27<=Y2)($C$1:$U$1<=X2)($C$2:$U$2=W2),$C$3:$U$27))

Hope this helps

M.

N0t Y0urs · Jun 1, 2022

Thanks I will try and work it backwards to understand it

N0t Y0urs · Jun 1, 2022

Thanks, that’s worked for the third column in each group but not the first two.

The first 2 columns need to add to the row reference. So in your example it would be 73 for column 1 at reference point 200

Marcelo Branco · Jun 1, 2022

N0t Y0urs said:
Thanks, that’s worked for the third column in each group but not the first two.

The first 2 columns need to add to the row reference. So in your example it would be 73 for column 1 at reference point 200

Why 73? I don't understand

Example with 2 first columns

01062022 Testes.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

2

3

4

5

Code

Groups

Row

Result

2

FWD

DB

RB

FWD

DB

RB

FWD

DB

RB

FWD

DB

RB

FWD

DB

RB

FWD

2

200

80

3

5

2

4

3

5

2

1

3

5

4

3

DB

2

200

75

4

10

4

3

1

5

2

3

1

5

2

4

5

4

3

2

RB

2

200

6

5

15

4

1

5

4

5

2

3

2

5

4

2

4

1

4

6

20

3

1

5

3

4

1

3

5

1

3

1

2

4

7

30

1

4

3

5

2

3

2

1

4

1

2

3

4

2

8

40

5

2

4

2

1

2

3

2

5

2

9

50

5

1

3

1

2

5

3

1

3

10

75

1

3

4

3

2

1

2

5

2

4

3

4

3

11

100

4

2

1

2

5

3

4

3

1

5

1

2

12

125

4

1

5

1

2

3

5

3

1

5

2

4

1

13

150

1

3

5

2

3

4

1

3

5

2

1

5

14

175

5

3

4

2

4

5

1

2

3

2

1

15

200

3

4

1

2

5

4

5

3

4

3

2

5

4

16

225

4

3

1

4

3

2

4

5

3

2

1

17

250

1

2

1

2

1

5

1

3

2

3

4

1

5

1

18

275

1

2

1

2

5

1

2

4

1

5

3

19

300

1

2

5

1

4

5

2

3

2

3

1

4

20

325

3

1

3

2

1

3

4

2

1

4

3

4

21

350

5

2

4

2

4

3

5

1

4

3

4

22

375

1

4

3

5

1

4

5

2

5

4

2

3

4

23

400

2

4

3

5

2

5

4

2

1

3

1

5

3

24

425

2

4

1

3

4

5

2

3

5

1

5

4

25

450

5

1

3

4

2

3

2

3

1

2

5

1

3

26

475

1

5

1

3

1

3

1

5

4

27

500

4

1

3

1

4

1

3

1

3

4

1

3

4

Plan4

Cell Formulas
Range	Formula
Z2:Z4	Z2	=IF(W2="RB",SUMPRODUCT(($B$3:$B$27=Y2)($C$1:$U$1<=X2)($C$2:$U$2=W2),$C$3:$U$27),SUMPRODUCT(($B$3:$B$27<=Y2)($C$1:$U$1<=X2)($C$2:$U$2=W2),$C$3:$U$27))

M.

N0t Y0urs · Jun 1, 2022

So if I use your example just groups 1 and 2

If I need row identifier 200 I need the total of column C to 200 if group 1 if group 2 it’s sum(c3:c15,g3:g15) etc

It’s the same for column D but Column E is just E15 for 1 or sum(e15,i15) for 2

Sorry if my earlier explanation wasn’t clear. But your formula works perfectly for the third column data requirements

Marcelo Branco · Jun 1, 2022

N0t Y0urs said:
If I need row identifier 200 I need the total of column C to 200 if group 1 if group 2 it’s sum(c3:c15,g3:g15) etc

C3:C15 + G3:G15 = 80 (see cell Z2)
Isn't this what you want?
If not, please explain further and tell us what results are expected in Z2, Z3 and Z4 when the number of groups = 2 (observe column X that is equal to the desired number of groups)

M.

Sumifs or index match

N0t Y0urs

Board Regular

Excel Facts

kweaver

Well-known Member

N0t Y0urs

Board Regular

Broken

N0t Y0urs

Board Regular

Marcelo Branco

MrExcel MVP

N0t Y0urs

Board Regular

N0t Y0urs

Board Regular

Marcelo Branco

MrExcel MVP

N0t Y0urs

Board Regular

Marcelo Branco

MrExcel MVP

Similar threads

Forum statistics

Share this page

Sumifs or index match

Board Regular

Excel Facts

Well-known Member

Board Regular

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

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