SUMIF QUESTION

DANLEY

New Member
Joined
Jul 5, 2006
Messages
12
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.
 

Some videos you may like

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 5, 2006
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Jul 5, 2006
Messages
12
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Jul 5, 2006
Messages
12
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Aug 29, 2004
Messages
819
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,812
Messages
5,544,458
Members
410,613
Latest member
Texman
Top