Sumifs or index match

N0t Y0urs

Board Regular
Joined
May 1, 2022
Messages
96
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. MacOS
  2. Mobile
  3. Web
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
It might help if you posted a sample of the data using XL2BB.
 
Upvote 0
Here is a link as I am pulling data from multiple sheets.

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


Thanks
 
Upvote 0
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
 
Upvote 0
Maybe something like this

Observe the group numbers in all columns (in gray)

Pasta1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1111222333444555CodeGroupsRowResult
2FWDDBRBFWDDBRBFWDDBRBFWDDBRBFWDDBRBFWD3200115
35243522111354443DB3200107
410431152315245432RB320011
515415455232542414
620315341135131244
730143523214122342
840522444212325222
950511331222531113
1075134332125224343
11100442112534315512
12125415122353152241
13150135233441352115
14175533424512232221
15200341255455343254
16225431444324532221
17250121211513234151
18275122122251241153
19300112251455232314
20325331332134211434
21350524244351114434
22375143514522542344
23400244352542131553
24425244133345235154
25450513442332312513
26475151133313315444
27500413141131341334
Plan4
Cell Formulas
RangeFormula
Z2:Z4Z2=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.
 
Upvote 0
Thanks I will try and work it backwards to understand it
 
Upvote 0
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
 
Upvote 0
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 :unsure:

Example with 2 first columns
01062022 Testes.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1111222333444555CodeGroupsRowResult
2FWDDBRBFWDDBRBFWDDBRBFWDDBRBFWDDBRBFWD220080
35243522111354443DB220075
410431152315245432RB22006
515415455232542414
620315341135131244
730143523214122342
840522444212325222
950511331222531113
1075134332125224343
11100442112534315512
12125415122353152241
13150135233441352115
14175533424512232221
15200341255455343254
16225431444324532221
17250121211513234151
18275122122251241153
19300112251455232314
20325331332134211434
21350524244351114434
22375143514522542344
23400244352542131553
24425244133345235154
25450513442332312513
26475151133313315444
27500413141131341334
Plan4
Cell Formulas
RangeFormula
Z2:Z4Z2=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.
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top