Sumproduct over Multiple Columns

[mucah!t]

Hello all,

I can't get the formula in G1 working.
It should sum all the numbers in the given range but ends up with a 81 instead of a 3 [see D17]

In the end I would like to deduct G1 from F1
Any ideas?

Excel Workbook
	B	C	D	E	F	G	H
1	10-Jan	18-Jan	AF		24	81
2
3		AF	AF	AF	DM	DM	DM
4	1-Jan
5	2-Jan
6	3-Jan
7	4-Jan
8	5-Jan
9	6-Jan
10	7-Jan
11	8-Jan
12	9-Jan
13	10-Jan	CD	C
14	11-Jan	CA	CN
15	12-Jan	C2	C3
16	13-Jan	ct	C
17	14-Jan		3
18	15-Jan
19	16-Jan
20	17-Jan
21	18-Jan
22	19-Jan
23	20-Jan
24	21-Jan
25	22-Jan
26	23-Jan
Sheet1

Excel 2003

Cell Formulas
Range	Formula
G1	=SUMPRODUCT((C3:H3=D1)*(B4:B41>=B1)*(B4:B41<=C1)*SUM(C4:H41))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[jasonb75]

=sumproduct((c3:h3=d1)*(b4:b41>=b1)*(b4:b41<=c1),c4:h41)

Your original formula was basically working on the lines of

(9 rows in date range)*(3 columns with matching criteria)*(sum(C4:H41)=3)

9 * 3 * 3 = 81

 

[mucah!t]

Thanks Jason!
I couldn't get it done myself