Sumif / sumproduct so many variables?

[drluke]

I have a data table that I want to summarise based on the cost centre in col H, but I don't
want to use a pivot table.
If I use a sumifs / sumproduct to sum to summarise the data in my summary table, how can I include all the Resource
cost centres (100, 112, 107, 230) in my formula?
I also have a separate sheet listing all the cost centres.
Data Sheet
	A	B	C	D	E	F	G	H	I
1	Nom	Loc	B/S	Doc No.	Doc date	Value in GBP	Yr/Prd	Cost Cen	Name
2	3100	15	035	219049	08/06/2018	-3,183.60	2018/5	100	Resource
3	3255	15	035	9692	06/04/2018	2,455.29	2018/5	200	Marketing
4	3304	26	020	240356	25/05/2018	59,499.98	2018/5	300	Sales
5	3330	50	015	240356	25/05/2018	158,807.51	2018/5	50	Corporate
6	3120	50	019	240356	25/05/2018	84,042.17	2018/5	400	Comms
7	3100	50	052	240356	25/05/2018	17,425.63	2018/6	112	Resource
8	3255	50	054	240356	25/05/2018	44,299.49	2018/6	200	Marketing
9	3304	50	055	240356	25/05/2018	36,807.56	2018/6	301	Sales
10	3330	50	064	240356	25/05/2018	22,250.00	2018/6	50	Corporate
11	3120	50	071	240356	25/05/2018	5,000.00	2018/6	400	Comms
12	3100	50	091	240356	25/05/2018	47,825.66	2018/7	107	Resource
13	3255	50	091	240395	07/06/2018	175,000.00	2018/7	200	Marketing
14	3304	50	091	14709	06/04/2018	2,455.29	2018/7	311	Sales
15	3330	50	091	9692	06/04/2018	-2,455.29	2018/7	50	Corporate
16	3120	50	095	240356	25/05/2018	10,250.00	2018/7	400	Comms
17	3100	500	031	240356	25/05/2018	26,632.67	2018/5	230	Resource
18	3255	75	020	240356	25/05/2018	290,623.99	2018/5	200	Marketing
19	3304	50	015	240356	25/05/2018	2,350.45	2018/5	318	Sales
20	3330	500	031	240356	25/05/2018	235.89	2018/5	50	Corporate
21	3120	500	031	240395	07/06/2018	175,000.00	2018/5	400	Comms
Summary Sheet
	A	B	C	D	E	F	G
1		Resourcing			Corporate
2		2018/5	2018/6	2018/7	2018/5	2018/6	2018/7
3	3100
4	3255
5	3304
6	3330
7	3120
Cost Centres
	A	B
1	100	Resourcing
2	112	Resourcing
3	107	Resourcing
4	230	Resourcing
5	512	Resourcing
6	300	Sales
7	301	Sales
8	311	Sales
9	358	Sales

<tbody>
</tbody>

 

[James006]

Hi,

It seems to me all your detailed cost centers codes do share a common name ... which you could use in your sumif formula ...

 

[drluke]

Have tried:

=SUMIFS(Data Sheet!$F$2:$F$21,Data Sheet!$A$2:$A$21,A3,Data Sheet!$G$2:$G$21,B2,Data Sheet!$I$2:$I$21,"Resource") and

=SUMIFS(Data Sheet!$F$2:$F$21,Data Sheet!$A$2:$A$21,A3,Data Sheet!$G$2:$G$21,B2,Data Sheet!$I$2:$I$21,B1).

Both result = 0

 

[James006]

Re,

Sure you have noticed your reference sheet uses Resourcing ... whereas your actual data apparently uses Resource ...

Have you checked the consistency of your formats ... i.e say 3100 is a Text ... in the Summary it should be exactly the same text ...