SUMIF QUESTION

[DANLEY]

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.

 

[barry houdini]

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]

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.

 

[Aladin Akyurek]

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]

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]

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]

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]

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]

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]

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!