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.
 

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
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,209
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!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top