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

LisaPal

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

Please help :)

Capture.PNG


Thanks in advance,
Lisa
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
Solution
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,

Thank you for your reply.

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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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