Sumif?

[Nanaia]

What formula do I use to have Excel look at 4 columns (D,E,F,G), the first three (D,E,F) are material size, the fourth (G) is the total of the parts for the material size. I figured it'd be SUMIF, but I can't figure out how to link the first three cells without using TEXTJOIN and creating a list of every possible size. I'd like it to group the results so I'll know what sizes there are and how many sheets are of each size.
Thanks for your help.
[TABLE="width: 129"]
<tbody>[TR]
[TD="colspan: 3"]SHEET SIZE[/TD]
[TD]#
SHEETS[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]144[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]144[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]72[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]x[/TD]
[TD]48[/TD]
[TD]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[Jonmo1]

First, if every cell in E is an x, then we can simply ignore it because it's irrelevant.

Try SUMIFS

=SUMIFS(G:G,D:D,108,F:F,48)

That will sum G for all 108 x 48


If the x in E is NOT something we can take for granted, then we can just add another criteria for it
=SUMIFS(G:G,D:D,108,E:E,"x",F:F,48)

 

[jtakw]

Hi,

The following is also ignoring the x in column E as explained by Jonmo:

Book1
	D	E	F	G	H
1	SHEET SIZE			#
2				SHEETS	Total
3	108	x	48	75	246
4	102	x	48	24	106
5	102	x	48	67
6	138	x	48	20	30
7	102	x	48	5
8	108	x	48	5
9	102	x	48	10
10	108	x	48	10
11	138	x	48	10
12	84	x	48	1	13
13	84	x	48	1
14	84	x	48	1
15	84	x	48	1
16	90	x	48	1	4
17	84	x	48	1
18	132	x	48	3	6
19	108	x	48	6
20	144	x	48	2	4
21	132	x	48	3
22	144	x	48	2
23	96	x	48	6	12
24	90	x	48	1
25	66	x	48	1	1
26	84	x	48	1
27	96	x	48	1
28	96	x	48	1
29	90	x	48	1
30	96	x	48	1
31	96	x	48	1
32	84	x	48	2
33	96	x	48	2
34	72	x	48	3	3
35	108	x	48	75
36	108	x	48	75
37	84	x	48	1
38	84	x	48	1
39	84	x	48	1
40	84	x	48	1
41	90	x	48	1
42	84	x	48	1
Sheet44

Cell Formulas
Range	Formula
H3	=IF(COUNTIFS(D$3:D3,D3,F$3:F3,F3)=1,SUMIFS(G$3:G$42,D$3:D$42,D3,F$3:F$42,F3),"")

H3 formula copied down as required, then use Filter to hide Blanks to show this (you can also hide column G):

Book1
	D	E	F	G	H
1	SHEET SIZE			#
2				SHEETS	Total
3	108	x	48	75	246
4	102	x	48	24	106
6	138	x	48	20	30
12	84	x	48	1	13
16	90	x	48	1	4
18	132	x	48	3	6
20	144	x	48	2	4
23	96	x	48	6	12
25	66	x	48	1	1
34	72	x	48	3	3
Sheet44