# 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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### 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
7
Views
49
Replies
2
Views
33
Replies
3
Views
27
Replies
3
Views
24
Replies
5
Views
53