# Sum if returning values when it should be 0?

#### MDuff

##### Well-known Member
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
LMNOP
11010101010
24041424344
3October (Preliminary)
410/1/200710/8/200710/15/200710/22/200710/29/2007
5233229224220215
6229224220215211
7404040
8
9
10500101295
11
Summary

Interval P_Hours Sheet saple data
Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
BCDE
2MonTueWedThurs
37/307/318/18/2
431313131
5
624331722
720281618
818231415
915181112
101213910
119977
127856
137745
146645
156745
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
2Mon
310/1
440
Interval P_Hours
Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls
BMBNBOBPBQBRBS
540000000
Interval P_Hours

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### thazan

##### Board Regular
Hi,

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

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

I hope this helps.

#### barry houdini

##### MrExcel MVP
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)

Replies
5
Views
248
Replies
4
Views
156
Replies
7
Views
302
Replies
4
Views
582
Replies
0
Views
124

1,191,025
Messages
5,984,201
Members
439,877
Latest member
kellylet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back