Best way to calculate INDEX/MATCH/SUMIFS??

[BeckyLH88]

Hello all,

General question, as I've been looking for different ways for a while and I've not come up with a solution yet!

I've pasted a copy of the data below as test data

So I want to look up the total if it's Nominal A and department 2 for example. I've tried sumifs but it doesn't seem to work as I'm using a horizontal and vertical data structure.

Whats the best formula to use?

Thanks,
Becky

Department	1​	2​	3​	4​	5​	6​	7​
Nominal Code
A	30​	10​			200​
B		9​		555​
C	2​
D						5​
E			8​	4​			2​

 

[BeckyLH88]

I'm using this formula, to then auto-populate a payroll journal for context

 

[Coyotex3]

Is this how your data is laid out? Nominal Code is listed directly below the Department column?

 

[BeckyLH88]

Is this how your data is laid out? Nominal Code is listed directly below the Department column?

In my data there is no row space where the heading nominal code is.

So Col a is my nominal code
row 1 is my department code and A2: K8 (I'm guessing at this range) is my data range to sum.

 

[Coyotex3]

This one threw me for a loop. Maybe an option would be using Sumproduct.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Nominal Code	1	2	3	4	5	6	7	8	9	10
2	A	0	174	151	0	0	78	108	135	81	198
3	B	85	136	163	33	164	91	98	138	199	182
4	C	20	66	182	117	111	98	165	26	79	156
5	D	159	81	21	190	40		61	132	35	35
6	E	177	0	6	188	188	27	164	191	91	152
7	F	161	63	172	131	3	84	117	147	137	184
8
9
10	174
Sheet1

Cell Formulas
Range	Formula
A10	A10	=SUMPRODUCT((A2:A7=A2)*(B1:K1=C1)*B2:K7)

 

[BeckyLH88]

Thank you I'll give it a go. I also need to add an indirect into the formula so I can change the tab references to look at different months. That will be my next step.

 

[Coyotex3]

Thank you I'll give it a go. I also need to add an indirect into the formula so I can change the tab references to look at different months. That will be my next step.

As that would be a different question, please post a new thread with the new requirements so that others may take a look at it and come up with possible solutions.