Sum if returning values when it should be 0?

[MDuff]

Sum if returning values when it should be 0?

I am using this formula to sum data biased on a number criteria (weeknum) the issue is it is giving me data when I should have a 0 Does any one have any idea why or a fix?
=SUMIF('Interval P_Hours'!$B$4:$IV$55,L2,'Interval P_Hours'!$B$54:$IV$54)

Issue Higlited in yellow

Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
	L	M	N	O	P
1	10	10	10	10	10
2	40	41	42	43	44
3	October (Preliminary)
4	10/1/2007	10/8/2007	10/15/2007	10/22/2007	10/29/2007
5	233	229	224	220	215
6	229	224	220	215	211
7			40	40	40
8
9
10	50	0	10	12	95
11
Summary

Interval P_Hours Sheet saple data

Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
	B	C	D	E
2	Mon	Tue	Wed	Thurs
3	7/30	7/31	8/1	8/2
4	31	31	31	31
5
6	24	33	17	22
7	20	28	16	18
8	18	23	14	15
9	15	18	11	12
10	12	13	9	10
11	9	9	7	7
12	7	8	5	6
13	7	7	4	5
14	6	6	4	5
15	6	7	4	5
Interval P_Hours

Interval P_Hours Sheet with Criteria 40 I would expect it to return a 0

Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
	BM
2	Mon
3	10/1
4	40
Interval P_Hours

Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
	BM	BN	BO	BP	BQ	BR	BS
54	0	0	0	0	0	0	0
Interval P_Hours

Thanks in advance

 

[thazan]

Hi,

I am not exactly sure what you are trying to do, but I usually use a sumproduct formula for this.

=sumproduct(('Interval P_Hours'!$B$4:$IV$4=L2)*( 'Interval P_Hours'!$B$54:$IV$54))

I hope this helps.

 

[MDuff]

Thanks that Sumproduct did work!!! Now just out of curiosity I corrected the ranges but they give me the same issue any idea why?

Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
	L	M	N	O	P
1	10	10	10	10	10
2	40	41	42	43	44
3	October (Preliminary)
4	10/1/2007	10/8/2007	10/15/2007	10/22/2007	10/29/2007
5	233	229	224	220	215
6	229	224	220	215	211
7			40	40	40
8
9
10	50	0	0	0	0
Summary

 

[barry houdini]

Can't see the formula in L10, what are you using now?

Surely you just need to replace 55 in your original with 4 so formula becomes

=SUMIF('Interval P_Hours'!$B$4:$IV$4,L2,'Interval P_Hours'!$B$54:$IV$54)