Advanced Sumproduct / Match

[ryansm05]

Hi,

I need a formula that will sum a range based on three criteria:

1) Project Code (D7:D17)
2) Region (C7:C17)
3) Period (E5:P5)

I tried a Sumproduct but it freaked out with three criteria :/ - I need this to obviously look at all three corresponding columns (dependent on whether it's Q1 etc)

https://www.dropbox.com/s/v5r0tsdksghli5w/SumHelp.xlsx?dl=0

 

[dave2018]

Why not sumifs?

 

[Marcelo Branco]

Maybe...

E21 copied across and down
=SUMPRODUCT(($C$7:$C$17=$C21)*($D$7:$D$17=$D21)*($E$5:$P$5=E$20),$E$7:$P$17)

M.

 

[wideboydixon]

Clickbait!

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5			Q1	Q1	Q1	Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
6	Area	Code
7	P&P	51					57	45	74
8	R&R	51
9	P&P	52	7	754	54	547	7	74	54	457	457	457
10	P&P	53	6	547		47	45		745			57
11	P&P	54
12	R&R	54
13	C&C	54		54	754				7	45	7	74	754	457
14	P&P	55		57	7754		4	7457			5474			47
15	C&C	56	574	4574	7		7		54		74	57	457
16	R&R	57				4754	754		75
17
18
19
20	Criteria 1	Criteria 2	Q1	Q2
21	P&P	52	815	628
22	R&R	54	0	0
23	R&R	57	0	5508
Sheet2

Cell Formulas
Range	Formula
E21	{=SUM(IF($C$7:$C$16=$C21,IF($D$7:$D$16=$D21,IF($E$5:$P$5=E$20,$E$7:$P$16))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
s	=#REF!#REF!

WBD

 

[ryansm05]

Clickbait!

	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5			Q1	Q1	Q1	Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
6	Area	Code
7	P&P	51					57	45	74
8	R&R	51
9	P&P	52	7	754	54	547	7	74	54	457	457	457
10	P&P	53	6	547		47	45		745			57
11	P&P	54
12	R&R	54
13	C&C	54		54	754				7	45	7	74	754	457
14	P&P	55		57	7754		4	7457			5474			47
15	C&C	56	574	4574	7		7		54		74	57	457
16	R&R	57				4754	754		75
17
18
19
20	Criteria 1	Criteria 2	Q1	Q2
21	P&P	52	815	628
22	R&R	54	0	0
23	R&R	57	0	5508

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula E21 {=SUM(IF($C$7:$C$16=$C21,IF($D$7:$D$16=$D21,IF($E$5:$P$5=E$20,$E$7:$P$16))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To s =#REF!#REF! <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WBD

@wideboydixon

What a fantastic formula - easy to follow/understand too.

Thanks so much!
Ryan