Formula to sum all the values in a range where the sum of two columns equals a third

[ferrethouse2004]

I'm conducting capacity planning for a strategic roadmap and for every row in H, I need the sum of all of G where C+D=F (for that particular row). So, for week 9 the capacity returned would be...

3 (7th row) because 4+5 = 9
plus 2 (10th row) because 4+5 = 9
resulting in a value of 5 in H for week 9.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Many people who may otherwise help you will bypass your question if they have to type out all the sample data to test.

Is this what you are after? (A couple of options)
If not, please provide (with XL2BB) some more sample data that includes the expected results manually filled in.

23 12 04.xlsm
	C	D		F	G	H	I
3	12	0		0	2	0	0
4	4	0		1	2	0	0
5	4	0		2	2	0	0
6	4	0		3	2	0	0
7	8	0		4	3	0	0
8	8	0		5	2	0	0
9	4	5		6	3	0	0
10	0	5		7	0	0	0
11	2	5		8	1	0	0
12	4	5		9	2	5	5
13	2	5		10	1	0	0
14	4	10		11	3	0	0
15	4	10		12	2	0	0
16	4	10		13	3	0	0
17	4	10		14	3	11	11
18	4	15		15	2	0	0
19	2	15		16	2	0	0
20	0	15		17	0	0	0
21	12	15		18	3	0	0
22	8	15		19	2	0	0
23	2	20		20	2	0	0
24	2	20		21	2	0	0
25	4	20		22	2	0	0
26	8	20		23	2	0	0
Capacity

Cell Formulas
Range	Formula
H3:H26	H3	=(C3+D3=F3)*SUMPRODUCT(--(C$3:C3+D$3:D3=F3),G$3:G3)
I3:I26	I3	=IF(C3+D3=F3,SUM(FILTER(G$3:G3,C$3:C3+D$3:D3=F3)),0)

 

[ferrethouse2004]

This worked. Thanks so much.

I actually only needed this part...

SUMPRODUCT(--(C$3:C3+D$3:D3=F3),G$3:G3)

 

[Peter_SSs]

This worked.

I actually only needed this part...

SUMPRODUCT(--(C$3:C3+D$3:D3=F3),G$3:G3)

Those two statements are contradictory since my formula and yours produce different results.
Your formula in col J below.

23 12 04.xlsm
	C	D		F	G	H	I	J
3	12	0		0	2	0	0	0
4	4	0		1	2	0	0	0
5	4	0		2	2	0	0	0
6	4	0		3	2	0	0	0
7	8	0		4	3	0	0	6
8	8	0		5	2	0	0	0
9	4	5		6	3	0	0	0
10	0	5		7	0	0	0	0
11	2	5		8	1	0	0	5
12	4	5		9	2	5	5	5
13	2	5		10	1	0	0	0
14	4	10		11	3	0	0	0
15	4	10		12	2	0	0	2
16	4	10		13	3	0	0	0
17	4	10		14	3	11	11	11
18	4	15		15	2	0	0	0
19	2	15		16	2	0	0	0
20	0	15		17	0	0	0	2
21	12	15		18	3	0	0	0
22	8	15		19	2	0	0	2
23	2	20		20	2	0	0	0
24	2	20		21	2	0	0	0
25	4	20		22	2	0	0	4
26	8	20		23	2	0	0	2
Capacity

Cell Formulas
Range	Formula
H3:H26	H3	=(C3+D3=F3)*SUMPRODUCT(--(C$3:C3+D$3:D3=F3),G$3:G3)
I3:I26	I3	=IF(C3+D3=F3,SUM(FILTER(G$3:G3,C$3:C3+D$3:D3=F3)),0)
J3:J26	J3	=SUMPRODUCT(--(C$3:C3+D$3:D3=F3),G$3:G3)

Perhaps I misunderstood the following requirement?

sum of all of G where C+D=F (for that particular row).

To me, C+D does not meet F in row 7, but I do see how that requirement could be interpreted differently.