Sum Index Match Using 2 Rows

[DisplayName]

Hello - I'm an Excel novice, and need help constructing an index match formula using 2 rows. I have 2 subtotal rows that I want to add if the date matches whatever month I set it too.

Right now I have this formula to return the current months total. I want the formula to look at 2 rows & add them, and return the current months total.

=INDEX($X$64:$AI$64,MATCH($AP$3,$X$7:$AI$7,0))

=index(operating exp row,match(MONTH,jul-jun row,0))

Hope this makes sense!

 

[AlanY]

you need to show as your data structure may be with a sample of the file

 

[DisplayName]

Month End	Apr-19
Months	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19
Employee 1	5,131	5,364	4,664	5,365	5,131	4,897	(9,002)	2,798	2,939	3,078	0	0
Employee 2	0	0	0	0	0	0	0	0	0	0	0	0
Employee 3	0	0	0	0	0	0	0	0	0	4,381	0	0
Employee 4	0	0	0	12,336	8,795	5,686	6,790	5,526	6,081	145	0	0
Employee 5	0	0	0	0	0	0	0	0	0	0	0	0
Employee 6	0	0	0	0	0	0	0	0	0	0	0	0
Employee 7	3,583	3,746	3,258	3,746	3,584	3,420	(1,628)	2,559	2,688	2,815	0	0
Employee 8	0	0	0	0	0	0	17,142	2,381	2,500	2,619	0	0
Employee 9	0	2,256	1,961	2,256	2,158	2,060	2,255	1,962	2,059	2,158	0	0
Employee 10	0	0	0	0	0	0	0	0	0	0	0	0
Employee 11	4,546	3,172	5,243	(6,301)	0	0	0	0	0	0	0	0
Total Salaries & Wages	13,260	14,538	15,126	17,402	19,668	16,063	15,557	15,226	16,267	15,196	0	0
Operating Exp 1	0	0	0	378	47	81	661	430	1,416	1,229	0	0
Operating Exp 2	0	0	0	0	35	0	0	88	0	0	0	0
Operating Exp 3	0	0	0	0	450	0	0	0	133	0	0	0
Operating Exp 4	0	0	0	0	0	0	545	0	0	0	0	0
Operating Exp 5	0	0	0	0	3,698	0	0	0	0	0	0	0
Operating Exp 6	0	0	0	0	0	0	0	0	0	0	0	0
Operating Exp 7	0	0	0	0	0	0	0	0	0	0	0	0
Operating Exp 8	0	199	144	57	0	0	152	117	0	107	0	0
Operating Exp 9	0	75	35	110	110	70	70	300	35	105	0	0
Operating Exp 10	0	0	0	0	0	51	0	0	0	0	0	0
Operating Exp 11	48	15	0	70	31	0	0	32	0	0	0	0
Operating Exp 12	0	0	0	0	0	0	558	0	0	0	0	0
Operating Exp 13	0	0	0	0	0	0	0	0	10,521	10,521	0	0
Total Operating Expenses	48	289	179	615	4,371	202	1,986	967	12,105	11,962	0	0

<colgroup><col><col span="3"><col><col span="2"><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>

Something like this. I want the index match formula to look at the 'total salaries & wages' row & 'total operating expenses' row, and add the two values that correspond to the 'month end' cell.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month End	Apr-19	27158
2
3	Months	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19
4	Employee 1	5,131	5,364	4,664	5,365	5,131	4,897	-9,002	2,798	2,939	3,078	0	0
5	Employee 2	0	0	0	0	0	0	0	0	0	0	0	0
6	Employee 3	0	0	0	0	0	0	0	0	0	4,381	0	0
7	Employee 4	0	0	0	12,336	8,795	5,686	6,790	5,526	6,081	145	0	0
8	Employee 5	0	0	0	0	0	0	0	0	0	0	0	0
9	Employee 6	0	0	0	0	0	0	0	0	0	0	0	0
10	Employee 7	3,583	3,746	3,258	3,746	3,584	3,420	-1,628	2,559	2,688	2,815	0	0
11	Employee 8	0	0	0	0	0	0	17,142	2,381	2,500	2,619	0	0
12	Employee 9	0	2,256	1,961	2,256	2,158	2,060	2,255	1,962	2,059	2,158	0	0
13	Employee 10	0	0	0	0	0	0	0	0	0	0	0	0
14	Employee 11	4,546	3,172	5,243	-6,301	0	0	0	0	0	0	0	0
15	Total Salaries & Wages	13,260	14,538	15,126	17,402	19,668	16,063	15,557	15,226	16,267	15,196	0	0
16
17	Operating Exp 1	0	0	0	378	47	81	661	430	1,416	1,229	0	0
18	Operating Exp 2	0	0	0	0	35	0	0	88	0	0	0	0
19	Operating Exp 3	0	0	0	0	450	0	0	0	133	0	0	0
20	Operating Exp 4	0	0	0	0	0	0	545	0	0	0	0	0
21	Operating Exp 5	0	0	0	0	3,698	0	0	0	0	0	0	0
22	Operating Exp 6	0	0	0	0	0	0	0	0	0	0	0	0
23	Operating Exp 7	0	0	0	0	0	0	0	0	0	0	0	0
24	Operating Exp 8	0	199	144	57	0	0	152	117	0	107	0	0
25	Operating Exp 9	0	75	35	110	110	70	70	300	35	105	0	0
26	Operating Exp 10	0	0	0	0	0	51	0	0	0	0	0	0
27	Operating Exp 11	48	15	0	70	31	0	0	32	0	0	0	0
28	Operating Exp 12	0	0	0	0	0	0	558	0	0	0	0	0
29	Operating Exp 13	0	0	0	0	0	0	0	0	10,521	10,521	0	0
30	Total Operating Expenses	48	289	179	615	4,371	202	1,986	967	12,105	11,962	0	0
Sheet1

Cell Formulas
Range	Formula
C1	=INDEX($A$3:$M$30,MATCH(A15,A3:A30,0),MATCH(B1,A3:M3,0))+INDEX($A$3:$M$30,MATCH(A30,A3:A30,0),MATCH(B1,A3:M3,0))

 

[DisplayName]

Worked like a charm! Thank you so much!

 

[Eric W]

For a somewhat different approach:

C1: =SUM(INDEX(B4:M30,0,MATCH(B1,B3:M3,0)))/2