# SUMIF QUESTION

#### DANLEY

##### New Member
How can I sum column M in the following scenario? I need to sumif(A:A,B3,M:M). But column M contains errors(I need to leave the errors). I tried to insert sumif column M is >0 in to the first formula but cant seem to get it to work.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### barry houdini

##### MrExcel MVP
Try

=SUM(IF(ISNUMBER(M1:M100),M1:M100)*(A1:A100=B3))

confirmed with CTRL+SHIFT+ENTER

adjust ranges as necessary but they can't be whole columns

#### DANLEY

##### New Member
Nope. Didnt work. But thanks for the reply.
Maybe I didnt explain well enough. I'll try again. I am on sheet 1. I need to sum column M on sheet 2, IF the value in column A sheet 2 is equal to the value in cell B3(sheet 1). So the exact formula would be:

=SUMIF(SHEET 2!A5:A2000,B3,SHEET 2!M5:M2000)

BUT, there are errors in column M sheet 2.

Thanks again.

##### MrExcel MVP
Nope. Didnt work. But thanks for the reply.
Maybe I didnt explain well enough. I'll try again. I am on sheet 1. I need to sum column M on sheet 2, IF the value in column A sheet 2 is equal to the value in cell B3(sheet 1). So the exact formula would be:

=SUMIF(SHEET 2!A5:A2000,B3,SHEET 2!M5:M2000)

BUT, there are errors in column M sheet 2.

Thanks again.

=SUM(IF('SHEET 2'!A5:A2000=B3,IF(ISNUMBER('SHEET 2'!M5:M2000),'SHEET 2'!M5:M2000)))

which must be confirmed with control+shift+enter, not just with enter.

#### barry houdini

##### MrExcel MVP

To use the formula I suggested you need to confirm with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

if your sheet name is "SHEET 2" with a space in it you need to refer to it with single quotes surrounding the name so the revised formula would be

=SUM(IF(ISNUMBER('SHEET 2'!M5:M2000),'SHEET 2'!M5:M2000)*('SHEET 2'!A5:A2000=B3))

note that this uses two separate functions, SUM and IF not SUMIF

#### DANLEY

##### New Member
Thanks again. I lied. I was trying to make it easy to understand. The name of the sheet I am referencing is "data". Here is the exact formula as I entered it:

{=SUM(IF(ISNUMBER(data!N\$4:N\$1994),data!N\$4:N\$1994)*(data!\$A\$4:\$A\$1994))}

I am getting this error: #VALUE!

#### barry houdini

##### MrExcel MVP

You don't have the =B3 in there, is that just a typo here or have you missed it in your formula?

=SUM(IF(ISNUMBER(data!N\$4:N\$1994),data!N\$4:N\$1994)*(data!\$A\$4:\$A\$1994=B3))

#### DANLEY

##### New Member
OOPS. Forgot part at the end.

{=SUM(IF(ISNUMBER(data!N\$4:N\$2000),data!N\$4:N\$2000)*(data!\$A\$4:\$A\$2000=\$A5))}

This returns 0. But there should be a number greater than 0.

#### barry houdini

##### MrExcel MVP
OOPS. Forgot part at the end.

{=SUM(IF(ISNUMBER(data!N\$4:N\$2000),data!N\$4:N\$2000)*(data!\$A\$4:\$A\$2000=\$A5))}

This returns 0. But there should be a number greater than 0.

I believe the formula should work, if it doesn't there may be a problem with your data. If the "numbers" in column N are actually text then this might work

=SUM(IF(ISNUMBER(data!N\$4:N\$2000+0),data!N\$4:N\$2000)*(data!\$A\$4:\$A\$2000=\$A5))

confirmed with CTRL+SHIFT+ENTER

or check that you are getting matches with A5

=COUNTIF(data!\$A\$4:\$A\$1994,A5)

#### vane0326

##### Well-known Member
See if this works.

=SUM(IF(data!\$A\$4:\$A\$2000=\$A5,IF(ISNUMBER(data!N\$4:N\$2000),(data!N\$4:N\$2000>0)*(data!N\$4:N\$2000)+0)))

Or

=SUM(IF(data!\$A\$4:\$A\$2000=\$A5,IF(ISNUMBER(data!N\$4:N\$2000),data!N\$4:N\$2000)+0))

Hope it helps!

Replies
2
Views
35
Replies
1
Views
108
Replies
2
Views
268
Replies
5
Views
309
Replies
2
Views
230

### Forum statistics

1,141,012
Messages
5,703,730
Members
421,312
Latest member
Mooncake1 ### 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