# Using INDEX MATCH to sum over a range, now wanting to add in an 'if' in to the formula

#### LisaPal

##### New Member
Hi,

Table 1 has many rows with costs in & in Table 2 I want to sum up these costs.

Originally Table 1 did not have currencies in, so I was able to use the below INDEX MATCH formula to do this:

=SUM(INDEX(2:6,N(IF(1,{1,2,3,2,5})),MATCH(C9,1:1,0))).

Now I want to split the total of these 5 rows, depending on currency (in col A).

I have come up with the below red formula - which works... But I need it to be more automatic (i.e. in case another row is inserted between row 2 and row 6, I don't want to have to update the INDEX & N formula each time).

Overall, my aim is that in cell C10, I want the formula to return all values in rows 2-6, where the date in row 9 matches the date in row 1, and the currency in col A matches too.

Lisa

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### KRice

##### Well-known Member
Try this to see if it does what you want:
Book1
ABCDE
1Table 1Date 1Date 2Date 3
2USDCost 1163
3EURCost 225
4USDCost 3346
5EURCost 4437
6GBPCost 5528
7
8
9Table 2Date 1Date 2Date 3
10USDTotal Cost4109
11EURTotal Cost687
12GBPTotal Cost528
Sheet1
Cell Formulas
RangeFormula
C10:E12C10=SUMIFS(C\$2:C\$6,\$A\$2:\$A\$6,\$A10)

Alternatively, you could shorten the formula slightly using the SUMIF function if there is only one match condition.

#### LisaPal

##### New Member
Try this to see if it does what you want:
Book1
ABCDE
1Table 1Date 1Date 2Date 3
2USDCost 1163
3EURCost 225
4USDCost 3346
5EURCost 4437
6GBPCost 5528
7
8
9Table 2Date 1Date 2Date 3
10USDTotal Cost4109
11EURTotal Cost687
12GBPTotal Cost528
Sheet1
Cell Formulas
RangeFormula
C10:E12C10=SUMIFS(C\$2:C\$6,\$A\$2:\$A\$6,\$A10)

Alternatively, you could shorten the formula slightly using the SUMIF function if there is only one match condition.
Hi KRice,

The example I have shown is a very simplified version of what I am actually working with. The actual spreadsheet I use as 'Table 1' is constantly edited, and amounts moved here, there and everywhere.

I had initially used a sumif, but the issue was that whenever anything was dragged and dropped in Table 1, then the formula in Table 2 would break.

Thanks,
Lisa

#### KRice

##### Well-known Member
Could you provide an example that shows what you mean...are you saying the dates in Table 1 and Table 2 don't always fall within the same column?...where something like this approach will find the matching dates:
MrExcel20210331.xlsx
ABCDEF
1Table 1Date 1Date 2Date 3Date 1
2USDCost 116311
3EURCost 225
4USDCost 3346
5EURCost 443712
6GBPCost 5528
7
8
9Table 2Date 3Date 1Date 2
10USDTotal Cost91510
11EURTotal Cost7188
12GBPTotal Cost852
Sheet1
Cell Formulas
RangeFormula
C10:E12C10=SUMPRODUCT(\$C\$2:\$F\$6,(\$A\$2:\$A\$6=\$A10)*(\$C\$1:\$F\$1=C\$9))

Last edited:

#### LisaPal

##### New Member
It seems I'm having a 'make things harder than it should be day'. I used the simple SUMIF and it worked. Thank you KRice.

#### KRice

##### Well-known Member
That's good news! Glad to help, and welcome to the Board!

Replies
13
Views
63
Replies
1
Views
185
Replies
1
Views
83
Replies
11
Views
365
Replies
1
Views
224

1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

### 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.

### Which adblocker are you using?

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

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