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

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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Solution

LisaPal

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

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
That's good news! Glad to help, and welcome to the Board!
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top