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

[LisaPal]

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

Thanks in advance,
Lisa

 

[KRice]

Try this to see if it does what you want:

Book1
	A	B	C	D	E
1	Table 1		Date 1	Date 2	Date 3
2	USD	Cost 1	1	6	3
3	EUR	Cost 2	2	5
4	USD	Cost 3	3	4	6
5	EUR	Cost 4	4	3	7
6	GBP	Cost 5	5	2	8
7
8
9	Table 2		Date 1	Date 2	Date 3
10	USD	Total Cost	4	10	9
11	EUR	Total Cost	6	8	7
12	GBP	Total Cost	5	2	8
Sheet1

Cell Formulas
Range	Formula
C10:E12	C10	=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]

Try this to see if it does what you want:

Book1
	A	B	C	D	E
1	Table 1		Date 1	Date 2	Date 3
2	USD	Cost 1	1	6	3
3	EUR	Cost 2	2	5
4	USD	Cost 3	3	4	6
5	EUR	Cost 4	4	3	7
6	GBP	Cost 5	5	2	8
7
8
9	Table 2		Date 1	Date 2	Date 3
10	USD	Total Cost	4	10	9
11	EUR	Total Cost	6	8	7
12	GBP	Total Cost	5	2	8
Sheet1

Cell Formulas
Range	Formula
C10:E12	C10	=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]

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
	A	B	C	D	E	F
1	Table 1		Date 1	Date 2	Date 3	Date 1
2	USD	Cost 1	1	6	3	11
3	EUR	Cost 2	2	5
4	USD	Cost 3	3	4	6
5	EUR	Cost 4	4	3	7	12
6	GBP	Cost 5	5	2	8
7
8
9	Table 2		Date 3	Date 1	Date 2
10	USD	Total Cost	9	15	10
11	EUR	Total Cost	7	18	8
12	GBP	Total Cost	8	5	2
Sheet1

Cell Formulas
Range	Formula
C10:E12	C10	=SUMPRODUCT($C$2:$F$6,($A$2:$A$6=$A10)*($C$1:$F$1=C$9))

 

[LisaPal]

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]

That's good news! Glad to help, and welcome to the Board!