Sum Multiple Columns Based column Criteria Range

[Biz]

Dear All,

In Excel 2010, I'm trying add multiple account numbers over multiple range.
I have tried using Sumproducts with Sumifs, but they fail when adding multiple columns.
There is an allocated certain account numbers which refers to certain projects like Project 1 (Cells I3:I6), Project 2 (Cells J3:J6) and Project 3 (Cell K3)

In cells B17:B19 I tried constructing my formula but it worked only Project 1 and Project 3 only. My should be answers can be found in range C17:C19.
Please note I need non array and non VBA or no Custom function solution. Excel formula is preferred.

Excel Sum Multiple Columns Based Column Criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Account Number	Col 1	Col 2	Col 3					Project 1	Project 2	Project 3
3	P8600-1-X101	-	-	-					P8600-1-X101	P8603-1-X101	Z101
4	P8600-1-X201	-	-	-					P8600-1-X201	P8603-1-X201
5	P8600-1-Y101	-	338,286.00	-					P8600-1-Y101	P8603-1-Y101
6	P8600-1-Y201	-	-	-					P8600-1-Y201	P8603-1-Y301
7
8	P8603-1-X101	-	5.00	-
9	P8603-1-X201	-	-	-
10	P8603-1-Y101	-	217,107.00	-
11	P8603-1-Y301	-	-	-
12	Z101	-	10.00	-
13
14
15
16			Should Be
17	Project 1	338,286.00	338,286.00
18	Project 2	-	217,112.00
19	Project 3	10.00	-
20
Sheet1

Cell Formulas
Range	Formula
B17	B17	=SUMPRODUCT((ISNUMBER(SEARCH($I$3:$I$6,$A$3:$A$12)))*($B$3:$D$12))
B18	B18	=SUMPRODUCT((ISNUMBER(SEARCH($J$3:$J$6,$A$3:$A$12)))*($B$3:$D$12))
B19	B19	=SUMPRODUCT((ISNUMBER(SEARCH($K$3,$A$3:$A$12)))*($B$3:$D$12))

Your help would be greatly appreciated.

Kind Regards

Biz

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Account Number	Col 1	Col 2	Col 3					Project 1	Project 2	Project 3
3	P8600-1-X101	0	0	0					P8600-1-X101	P8603-1-X101	Z101
4	P8600-1-X201	0	0	0					P8600-1-X201	P8603-1-X201
5	P8600-1-Y101	0	338286	0					P8600-1-Y101	P8603-1-Y101
6	P8600-1-Y201	0	0	0					P8600-1-Y201	P8603-1-Y301
7
8	P8603-1-X101	0	5	0
9	P8603-1-X201	0	0	0
10	P8603-1-Y101	0	217107	0
11	P8603-1-Y301	0	0	0
12	Z101	0	10	0
13
14
15
16			Should Be
17	Project 1	338286	338286
18	Project 2	217112	217112
19	Project 3	10	0
20
Sheet1

Cell Formulas
Range	Formula
B17	B17	=SUMPRODUCT(--ISNUMBER(MATCH($A$3:$A$12,I$3:I$6,0))*($B$3:$D$12))
B18	B18	=SUMPRODUCT(--ISNUMBER(MATCH($A$3:$A$12,J$3:J$6,0))*($B$3:$D$12))
B19	B19	=SUMPRODUCT(--ISNUMBER(MATCH($A$3:$A$12,K$3:K$6,0))*($B$3:$D$12))

 

[Biz]

Hi M8,

Thank you very for your help.
Have a very nice weekend.

Kind Regards

Biz

 

[maabadi]

You're Welcome & Thanks for Follow-up