Sumifs, Index, formula only summing first value

[Excel_help_in_need]

Hi, Thank you for any help!

I am trying to sum quantities in a data entry table by three criteria; hazardous/non-hazardous, type of waste and process method. Additionally it needs to sum this across the multiple tabs, all with same data entry format. I have used below formula, which is repeated multiple times for the number of data entry sheets;

SUMIFS(INDIRECT("'"&$C$3&"'!$AH$25:$AH$481"),INDIRECT("'"&$C$3&"'!$E$25:$E$481"),Consolidation!$C65,INDIRECT("'"&$C$3&"'!$G$25:$G$481"),Consolidation!G$49,INDIRECT("'"&$C$3&"'!$F$25:$F$481"),Consolidation!$D$61)

It works in that it is returning the values based on the criteria, but it is stopping at the first time it finds the criteria, e.g., sum all quantities that are 1, plastic waste, 2, hazardous, 3, recycled.

How do I get it to sum all that meet the criteria so data is not missed in my summary table?

Thankyou

 

[etaf]

are you trying to do effectively a 3d sumif

that works for me and somes the values that are in the sheet named in c3

perhaps this may help

SUMIF with 3D Reference in Excel

Learn how to use Excel 3D referencing with SUMIF function. The normal 3D referencing return #VALUE! with the SUMIF function. The SUMIF and 3D INDIRECT is used.

www.exceltip.com

perhaps a sample example would help here
I have a small sample - BUT it will only sum wants in Sheet3 - as thats what value is in C3
i only keep on dropbox for a few days

3dsum-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

3dsum-ETAF.xlsx
	A	B	C
1
2	4
3			sheet3
consolidation

Cell Formulas
Range	Formula
A2	A2	=SUMIFS(INDIRECT("'"&$C$3&"'!$AH$25:$AH$481"),INDIRECT("'"&$C$3&"'!$E$25:$E$481"),consolidation!$C65,INDIRECT("'"&$C$3&"'!$G$25:$G$481"),consolidation!G$49,INDIRECT("'"&$C$3&"'!$F$25:$F$481"),consolidation!$D$61)

3dsum-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1																																		SUMIFS(INDIRECT("'"&$C$3&"'!$AH$25:$AH$481"),INDIRECT("'"&$C$3&"'!$E$25:$E$481"),Consolidation!$C65,INDIRECT("'"&$C$3&"'!$G$25:$G$481"),Consolidation!G$49,INDIRECT("'"&$C$3&"'!$F$25:$F$481"),Consolidation!$D$61)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25					a	2	1																											1
26					b	2	1																											2
27					a	2	1																											3
28					b	2	1																											4
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
Sheet3